I have a module wide variable at the top of the userform module (Dim rng As Range), however if Userform is shown then i can not access that variable (throws error)
If I click a second button then that can read the public variable content without error. Why hiding and showing userform causes error while reading public variable in userform module?
If I click a second button then that can read the public variable content without error. Why hiding and showing userform causes error while reading public variable in userform module?
VBA Code:
Private Sub CommandButton1_Click()
UserForm1.Hide
Call r
UserForm1.Show
MsgBox rng.Address 'only works if hiding and showing is commented out, however from different procedure it can read that variable without error
End Sub
Private Sub CommandButton2_Click()
MsgBox rng.Address
End Sub
Sub r()
On Error Resume Next
Set rng = Application.InputBox( _
Title:="Title", _
Prompt:="Prompt", _
Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
rng.Interior.Color = VBA.ColorConstants.vbGreen
End Sub