bring back a userform after a macro is run

still learning · Jul 30, 2017

Hi
I’m trying to have userform1 come back after a macro is run
The macro is a message box
I can get it to run. But I don’t know how to bring back the userrform after.
List box1 is Letters A to Z
I tried >>userform1. show<< after the message in the "nl" macro but i get an error
I also tried >>hide.me<< after Case Is = "E": nl

HTML:

Private Sub CommandButton4_Click()

  Select Case Me.ListBox1.Value
  Case Is = "A":
      Application.Goto Reference:="DA", Scroll:=True
  Case Is = "B":  Application.Goto Reference:="DB", Scroll:=True
  Case Is = "C":  Application.Goto Reference:="DC", Scroll:=True
  Case Is = "D":  Application.Goto Reference:="DD", Scroll:=True
   
  Case Is = "E":  nl    ‘here is where i want the macro “nl to run then after “ok”,
        have the Userform1 come up again to pick another letter
      
  Case Is = "G”:  Application.Goto Reference:="DG", Scroll:=True
  Case Is = "H":  Application.Goto Reference:="DH", Scroll:=True
  Case Is = "I":  Application.Goto Reference:="DI", Scroll:=True
  Case Is = "I":  nl   ‘ like above 
  End Select
  Unload Me
  End Sub

HTML:

Sub nl()

   MsgBox "        No site with that Letter"
  UserForm1.Show  ' this makes the line red
  End Sub

Mike

iliace · Jul 30, 2017

I don't think you should need to bring it back, since it's already displayed. If it's showing, then UserForm1.Show will cause an error, so just take that line out. Try something like instead:

Rich (BB code):

Private Sub CommandButton4_Click()
  Dim bUnload As Boolean


  bUnload = True
  Select Case Me.ListBox1.Value
  Case Is = "A":
      Application.Goto Reference:="DA", Scroll:=True
  Case Is = "B":  Application.Goto Reference:="DB", Scroll:=True
  Case Is = "C":  Application.Goto Reference:="DC", Scroll:=True
  Case Is = "D":  Application.Goto Reference:="DD", Scroll:=True
   
  Case Is = "E":  nl
    bUnload = False
      
  Case Is = "G":  Application.Goto Reference:="DG", Scroll:=True
  Case Is = "H":  Application.Goto Reference:="DH", Scroll:=True
  Case Is = "I":  Application.Goto Reference:="DI", Scroll:=True
  Case Is = "I":  nl
    bUnload = False
  End Select
  If bUnload Then Unload Me
End Sub

still learning · Aug 1, 2017

Thank you iliace

It works perfect with your add-ons

sorry it took so long for a response. I changed my internet provider and had problems.
Hopefully it's all fixed.

Along the same lines...
How would I change the following if I wanted to click on a box in the userform that said all and print each range?
as you see there are some skipped numbers.
I'm only showing a portion of the code to save space

HTML:

Case Is = "A": ActiveSheet.PrintOut From:=4, To:=5, Copies:=1
Case Is = "B": ActiveSheet.PrintOut From:=7, To:=8, Copies:=1
Case Is = "C": ActiveSheet.PrintOut From:=10, To:=11, Copies:=1
Case Is = "D": ActiveSheet.PrintOut From:=13, To:=15, Copies:=1

I tried
Case Is = "ALL" ActiveSheet.PrintOut, Fom:4, To:=5, From:7, To:8, etc
but got a red line

Mike

iliace · Aug 26, 2017

In that situation, you should probably have a Sub defined for each of the print cases. As an example:

Code:

Private Sub PrintA()
  ActiveSheet.PrintOut From:=4, To:=5, Copies:=1
End Sub

For your cases, it would be something like this:

Code:

Case "A": PrintA
Case "B": PrintB
Case "C": PrintC
Case "D": PrintD
Case "ALL"
  PrintA: PrintB: PrintC: PrintD

bring back a userform after a macro is run

still learning

Well-known Member

Excel Facts

iliace

Well-known Member

still learning

Well-known Member

iliace

Well-known Member

Similar threads

Forum statistics

Share this page

bring back a userform after a macro is run

still learning

Well-known Member

Excel Facts

iliace

Well-known Member

still learning

Well-known Member

iliace

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock