Userform with inputbox - error if userform is "shown"

Jamualson

Board Regular
Joined
Feb 17, 2021
Messages
145
Office Version
  1. 2019
Platform
  1. Windows
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?

1618858416939.png


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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
What is the error message?
 
Upvote 0
What is the error message?
Sorry 1 info:
- the msgbox part is not running in first sub at all (only userform showing again and thats all, as if code would stop at that line)
- and when closing userform, comes the error to that line i marked with red (variable not set)

Thank you very much for your idea
 
Upvote 0
Assuming your userform is modal, code will always stop after a Show line and wait for the form to be hidden or unloaded. When you unload it, the code will continue, but your variable has been reset as the form is unloading. If you move the variable to a normal module, it should remove the issue, but this isn't really good design.
 
Upvote 0
Solution
Assuming your userform is modal, code will always stop after a Show line and wait for the form to be hidden or unloaded. When you unload it, the code will continue, but your variable has been reset as the form is unloading. If you move the variable to a normal module, it should remove the issue, but this isn't really good design.
thank you very much for this great piece of help. I tried to combine inputbox with userform so dont need to add reference to refedit.

Do you have maybe some instructions how could i combine inputbox with userform?

So if user clicks a button, userforms hides ans inputbox reveals, then after choosing range userform comes back and work can continue.

I dont know how could i make my code to handle your critic. Thank you very much
 
Upvote 0
It would depend on your workflow and circumstances. Does the form need to be modal? Why don't you want to use the RefEdit (you don't actually need a reference)? Is it possible to structure the code so that the code that loads the form initially can re-show it after the range is selected?
 
Upvote 0
It would depend on your workflow and circumstances. Does the form need to be modal? Why don't you want to use the RefEdit (you don't actually need a reference)? Is it possible to structure the code so that the code that loads the form initially can re-show it after the range is selected?
Well, this is all the code I included in the post, and yes, it would be great if user could move freely on the worksheet until selecting range.

So I dont really have any other idea. I dont know how to use refedit without manually adding reference but will take a look. thank you very much
 
Upvote 0
In that case, make the form modeless and you should be fine.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top