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

 

[John_w]

If AlphaChoice has workbook scope:

    Range("AlphaChoice").ClearContents

Otherwise if AlphaChoice has worksheet scope and Data is not the active sheet:

    Worksheets("Data").Range("AlphaChoice").ClearContents

 

[DMumme]

Thank you for your response. I adjusted my macro according to your suggestion and nothing changes. Clearing the contents of the cell "AlphaChoice" has not been the problem. Loading "AlphaChoice" with the selection from the User Form while the macro is in "Run Sub" mode appears to be. "AlphaChoice" has the selection after the macro finishes but appears not to have it after the point of the User Form selection but prior to continuing the macro operation.

 

[RoryA]

Did you change the code in the Userform too?

 AlphaChoiceRowNum = Sheets("Data").Range("AlphaChoiceRowNum")

 

[DMumme]

Thank you. I have now - still no change. Not to get off topic but I have often used the format of 'Data'!AlphaChoiceRowNum in macros as opposed to Sheets("Data").Range("AlphaChoiceRowNum") and it has never been an issue in the past. Also, both formats work fine while in "Step Into" mode but fail in "Run Sub" mode.

 

[RoryA]

That syntax will not work if the code is in the worksheet code module of a different sheet, unless you specify Application.Range and not just Range, which is why I would never recommend it.

What else does the UF1AlphaSelect form do?

 

[DMumme]

I have been able to pinpoint the "Exception occurred." error as happening immediately upon choosing a letter in the User Form.

 

[DMumme]

Interesting. The entire code for the Macro and User Form is in the original post having only been amended for the suggestions in this thread.
The User Form is a list consisting of each letter of the alphabet. That's it.

 

[DMumme]

The problem appears to be in attempting to make the click in the list box within the User Form continue the execution of the macro without requiring a 2nd click at the User Form level. When I change the name of the User Form Macro to Private Sub UserForm_Click() and click within the User Form but outside of the ListBox after making my List Box selection it works like a charm. I suppose I will need a OKAY or CONT. button there so that it will be understood. If you can figure a way to make Private Sub AlphaListBox_Click() do the deed that would be cool. Thanks for your input.

 

[RoryA]

When the macro fails during the "Run Sub" mode it appears that the chosen letter was not stored at 'Data'!AlphaChoice.

There is no code to store the selected value anywhere other than in a variable. Is your listbox linked directly to a cell?