Excel VBA Macro works on Step Into Mode but not in Run Sub Mode

[DMumme]

When running a macro in "Step Into" mode it performs correctly. When running the same macro in "Run Sub" mode it fails every time. In the "Run Sub" mode I receive an "Exception occurred." error message followed by "Run-time error '1004': Method 'Range' of object'_Global' failed" message.

I am very experienced in Excel and have written many complex macros. However, I am entirely self-taught and not well informed on the terminology or the reasons for the coding working the way it does. I am certainly not strong with User Forms.

The purpose of this macro is to send the user to a particular part of an alphabetically sorted list by the selecting the first letter from a User Form. The user form stores the chosen letter in a cell name "AlphaChoice" on the Data sheet. A second cell named "AlphaChoiceRowNum" has a formula that determines on which row the first word with that starting letter is found. The macro then takes the user to column A in the row number from that second cell. Pretty simple. When the macro fails during the "Run Sub" mode it appears that the chosen letter was not stored at 'Data'!AlphaChoice.

Immediately below is the code for the macro followed by the code ran by clicking in the User Form. Any help would be appreciated.

MACRO
=============

Sub RowFinder()


    Range("'Data'!AlphaChoice").ClearContents


    UF1AlphaSelect.Show


    AlphaChoiceRowNum = Range("'Data'!AlphaChoiceRowNum")
    
    GoToLocation = "A" & AlphaChoiceRowNum
    
    Application.Goto Range(GoToLocation), True
       
End Sub

USER FORM
=================

Private Sub AlphaListBox_Click()


    AlphaChoiceRowNum = Range("'Data'!AlphaChoiceRowNum")
    UF1AlphaSelect.Hide
    
End Sub

 

[DMumme]

The properties for the AlphaListBox has a ControlSource of AlphaChoice. Is that not the proper way to link the selection to a cell?

 

[RoryA]

It depends on your point of view. Personally, I never link controls directly to cells as it makes it a little unpredictable as to when their events may fire. I prefer to use code to populate both the controls and their related cells.

 

[DMumme]

Does your response indicate that you would leave the ControlSource property of the ListBox blank? Would you provide an example of code that would populate both the controls and their related cells?

 

[RoryA]

Yes, I never use ControlSource or Rowsource. To populate a Listbox I use its List property - for example:

Me.Listbox1.list = Sheets("some sheet").Range("A1"A50").Value

then to assign a value, you simply use:

Application.Range("'Data'!AlphaChoiceRowNum").Value = me.listbox1.value

or similar.