Forum Discussion

cking1333's avatar
cking1333
Copper Contributor
Jul 23, 2024
Solved

When checkbox selected, enter specific value into spreadsheet

Greetings, 

 

I have a userform that contains multiple checkboxes that, when checked, I would like to enter "Y" or "N" into a spreadsheet. I'm getting an error when I use an IF statement on line 18 below and am not sure why. 

 

Any assistance would be appreciated as I have numerous checkboxes on various forms that will obviously cause an issue moving forward. 

 

Thanks!

 

  With Sheets("TIER ACCESS")
    'copy and pasted into "Sheet2" on the last row without text.
    lr = .Range("A" & Rows.Count).End(3).Row + 1
    .Range("A" & lr).Value = FirstName.Value & " " & LastName.Value ' USER NAME
    .Range("B" & lr).Value = OPSEmail.Value ' PASSWORD
    .Range("C" & lr).Value = OPSEmail.Value ' EMAIL ADDRESS
    .Range("D" & lr).Value = Date & " " & Time ' DATE USER ADDED
    .Range("E" & lr).Value = Sheets("HOME PAGE").Range("M3").Value ' USER ADDED BY
    .Range("F" & lr).Value = "Yes" ' ACTIVE USER
    .Range("G" & lr).Value = "TIER 1" ' USER TIER
    .Range("H" & lr).Value = "PURGE, ADD, MODIFY" ' CLIENT PROFILE PERMISSIONS
    .Range("I" & lr).Value = "NO PERMISSIONS" ' NON-CLIENT PROFILE PERMISSIONS
    .Range("J" & lr).Value = "N" ' UserAccess
    .Range("K" & lr).Value = "N" ' TIER ACCESS WS
    .Range("L" & lr).Value = "N" ' LIST WS
    .Range("M" & lr).Value = "N" ' CLIENTS WS
    .Range("N" & lr).Value = "Y" ' CLIENT PROFILES WS
    .Range("O" & lr).Value = if DTTCheckBox.Value = true then "Y" ' DTT WS
    .Range("P" & lr).Value = "Y" ' TECHNOLOGY WS
    .Range("Q" & lr).Value = "Y" ' UNIFORM WS
    .Range("R" & lr).Value = "Y" ' LiDAR & RADAR WS
    .Range("S" & lr).Value = "Y" ' VEHICLES WS
    ' .Range("" & lr).Value = ""
    ' .Range("" & lr).Value = ""
    ' .Range("" & lr).Value = ""
    ' .Range("" & lr).Value = ""
    ' .Range("" & lr).Value = ""
    ' .Range("" & lr).Value = ""
    ' .Range("" & lr).Value = ""
    ' .Range("" & lr).Value = ""

 

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jul 23, 2024

    cking1333 

    You cannot use If ... Then this way.

    You can use an If .. Then block:

        If DTTCheckBox.Value = True Then
        .Range("O" & lr).Value = "Y"
    Else
        .Range("O" & lr).Value = "N"
    End If

    Or you can use the IIf (Immediate If) function:

        .Range("O" & lr).Value = IIf(DTTCheckBox.Value = True, "Y", "N")

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 23, 2024

    cking1333 

    You cannot use If ... Then this way.

    You can use an If .. Then block:

        If DTTCheckBox.Value = True Then
        .Range("O" & lr).Value = "Y"
    Else
        .Range("O" & lr).Value = "N"
    End If

    Or you can use the IIf (Immediate If) function:

        .Range("O" & lr).Value = IIf(DTTCheckBox.Value = True, "Y", "N")

Resources