Userform_Initialize() Dim Range prevents userform from opening on other worksheets

[markddelorme]

I'm creating a userform (SOP421) that will serve as a "quiz builder"; i.e. the label captions and option button captions are pulled from specific cells in Sheet3("TRAINING") so that the questions and available answers can be modified from within the worksheet.

Initially, I got the form working perfectly, and it was able to be shown on any worksheet that I would call it to with the use of a simple macro. Things got a little more complex within my workbook, and as a result, I needed to have the specific cells mentioned above become "dynamic". Basically, if any rows were added or deleted above the target cells, the form needed declared dimensions to allow the specific cells that held the form captions to be found and referenced.

This is pretty difficult to explain... so I'll just include my code below. I've pin-pointed the problem to the Dim declarations right at the start. If I remove this declarations and replace the variables with static ranges in the rest of the code, the form initializes fine on any worksheet. With the code the way it is copied below, when I try to run the macro to show the form on any sheet OTHER than Sheet3 (it does work on Sheet3!), I get the following error...

ERROR: [Run-time error '1004': method 'range' of object '_worksheet' failed]
> This then leads me back to my macro:
Sub OPEN()
SOP421.Show (this line is highlighted in debug mode)
End Sub

---

Private Sub UserForm_Initialize()

'POPULATE FORM FIELDS
Dim SOPRange As Range
Dim SOPRow As Range
    Set SOPRange = Sheet3.Range("A1", Range("A65536").End(xlUp))
    Set SOPRow = SOPRange.Find("SOP421", LookIn:=xlValues, lookat:=xlWhole)
    
lblQ1.Caption = "(#1)     " & Sheet3.Range("B" & SOPRow.Row + 1)
lblQ2.Caption = "(#2)     " & Sheet3.Range("B" & SOPRow.Row + 6)
lblQ3.Caption = "(#3)     " & Sheet3.Range("B" & SOPRow.Row + 11)
lblQ4.Caption = "(#4)     " & Sheet3.Range("B" & SOPRow.Row + 16)

    obQ11.Caption = Sheet3.Range("W" & SOPRow.Row + 1)
    obQ12.Caption = Sheet3.Range("W" & SOPRow.Row + 2)
    obQ13.Caption = Sheet3.Range("W" & SOPRow.Row + 3)
        If Sheet3.Range("W" & SOPRow.Row + 3).Value = "" Then
        obQ13.Visible = False
        End If
    obQ14.Caption = Sheet3.Range("W" & SOPRow.Row + 4)
        If Sheet3.Range("W" & SOPRow.Row + 4).Value = "" Then
        obQ14.Visible = False
        End If

End Sub

Any help would be greatly appreciated, as this is causing me a major headache to try and figure out from reading all kinds of semi-related issues on the web. Cheers!

 

[markddelorme]

I just solved my problem! I don't really understand how this solved it, but it seems to have done the trick.

All it took was for me to change this line of code:

Set SOPRange = Sheet3.Range("A1", Range("A65536").End(xlUp))

... to this instead:

Set SOPRange = Sheet3.Range("A:A")

 

[sericom]

Try

Set SOPRange = Sheet3.Range("A1", [COLOR=#ff0000]Sheet3.[/COLOR]Range("A65536").End(xlUp))

 

[markddelorme]

Try

Set SOPRange = Sheet3.Range("A1", [COLOR=#ff0000]Sheet3.[/COLOR]Range("A65536").End(xlUp))

Thanks sericom! I did try this, and it also worked. I'm more inclined to keep the code as small as possible, and since

[...]Sheet3.Range("A:A")

seems to be working without flaw, I'll stick with this. I appreciate the valid solution you provided! Go figure, an hour ago I was hopeless, now I have 2 solutions.