UserForm event-handler transferring responses to worksheet

Question

This is a truly beginner question on using UserForms. I'm trying to follow an example from Walkenbach's Excel 2013 Power Programming with VBA and am not getting the desired response during event-handling. I'm hoping someone here can help me figure out where I've gone awry.

So, the idea is to have a UserForm that collects information on a name via a TextBox and a favorite color via a series of OptionButtons. Additional objects are a Label, Frame, and two CommandButtons ("OK" and "Cancel").

Properties that differ from default for the controls are as follows:

Label control:
 Accelerator: N
 Caption: Name:
 TabIndex: 0
TextBox control:
 Name: TextName
 TabIndex: 1
Frame control:
 Caption: Favorite Color
 TabIndex: 2
OptionButton controls inside Frame:
 (1)
   Accelerator: B
   Caption: Blue
   Name: OptionBlue
   TabIndex: 0
 (2)
   Accelerator: R
   Caption: Red
   Name: OptionRed
   TabIndex: 1
 (3)
   Accelerator: Y
   Caption: Yellow
   Name: OptionYellow
   TabIndex: 2
CommandButton control ("OK"):
 Caption: OK
 Default: True
 Name: OKButton
 TabIndex: 3
CommandButton control ("Cancel"):
 Caption: Cancel
 Name: CancelButton
 TabIndex: 4

The problem comes in the event-handling for the OK button. I use the following code:

 Private Sub OKButton_Click()
    Dim NextRow As Long
    'Make sure Sheet1 is active
    Sheets("Sheet1").Activate

    'Determine the next empty row
    NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1
    'Transfer the name
    Cells(NextRow, 1) = TextName.Text

    'Transfer the gender
    If OptionBlue Then Cells(NextRow, 2) = "Blue"
    If OptionRed Then Cells(NextRow, 2) = "Red"
    If OptionYellow Then Cells(NextRow, 2) = "Yellow"

    'Clear the controls for the next entry
    TextName.Text = ""
    OptionUnknown = True
    TextName.SetFocus

End Sub

The code correctly outputs the name to the worksheet, but does not seem to perform the if-statements correctly (i.e. no output to the worksheet). After running the UserForm and entering some dummy data, my sheet looks like: Output after attempting to input names and favorite colors

Can anyone help me determine why this code isn't working?

FYI - the CancelButton subroutine just does Unload UserForm1 and the "Show UserForm" button simply makes the UserForm pop up.

Thanks for your help.

I assume that Cells(NextRow, 1) = TextName.Text correctly assigns the text to column A? If so; 1) Use Option Explicit in this module and 2) make sure the name of your controls is what you think it is. — David Zemens
I duplicated this userform as described and copied in the code exactly, and it worked as expected, so I would guess that, like David Zemens suggests, the issue is that there's a typo somewhere in the names of your controls. — Liza
Simple way to check is to use If Me.OptionBlue Then and so on. You'll get Intellisense for the control names and an error if they're not correct, even if you don't have Option Explicit (which you should always use!) — Rory
Thanks for your responses. I hadn't tried quitting and reopening Excel, which apparently did the trick, because with no change in the code, it's running as expected today. Not sure what happened there. Thank you for your time. — Ebsith

wellimustbedead wellimustbedead · Accepted Answer · 2015-05-08T18:44:45

Your code relies on default properties being used. To create a more robust piece of code it is best to declare the property you wish to use which means the if statements would become something like:

If OptionBlue.Value Then Cells(NextRow, 2).Value = "Blue"
If OptionRed.Value Then Cells(NextRow, 2).Value = "Red"
If OptionYellow.Value Then Cells(NextRow, 2).Value = "Yellow"

It is only a minor (and normally unnessecary) alteration, but depending on the references being used by your project, this can prevent the code being falsely interpreted.

UserForm event-handler transferring responses to worksheet

1 Answers