Link a Macro for button of a User Form on Excel-HELP!

[suiliclic]

Hello All,

Please help~I got stuck on this problem for a couple days already. I appreciate your insightful input.

On excel, I created a macro under Tools-->Macros-->Record New Macro.. to record a chain of actions from turning on auto filter to print preview. If I choose to run this macro manually under Tools --> Macros -- Macros..-->Run, everything will work properly.

I created an User Form with a button that suppose to triger the macro I recorded. However, every time I click on the button on that End User Form, my screen would be froze and my VBA screen will say (running) on top of it. I have to use "Ctrl+Alt+Delete" to kill it. I checked on all the codes for that Macro (which was stored in a module), it doesn't look like I got any infinate loop. So, I am wondering if there is anything has to do with the codes under the Button (Module5.A1PrintPreviewFlagY)?

So here is the codes of my Macro which is stored in a module. (It's stored in Module5 by default when I recorded using excel's -->Tools-->Record Macro)
----------------------------------------------------------
Sub A1PrintPreviewFlagY()

Rows("1:1").Select
Selection.AutoFilter

Columns("A:BM").Select
Selection.EntireColumn.Hidden = False
Columns("G:I").Select
Selection.EntireColumn.Hidden = True
Columns("M:N").Select
Selection.EntireColumn.Hidden = True
Columns("P").Select
Selection.EntireColumn.Hidden = True
Columns("R:R").Select
Selection.EntireColumn.Hidden = True
Columns("U:V").Select
Selection.EntireColumn.Hidden = True
Columns("X:X").Select
Selection.EntireColumn.Hidden = True
Columns("AC:AL").Select
Selection.EntireColumn.Hidden = True
Columns("AN:BC").Select
Selection.EntireColumn.Hidden = True
Columns("BE:BG").Select
Selection.EntireColumn.Hidden = True

Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="Y"
Columns("A:BI").Select
Range("BI1").Activate
ActiveSheet.PageSetup.PrintArea = "$A:$BI"
ActiveWindow.SelectedSheets.PrintPreview
End Sub
--------------------------------------------------------
And here is the code for the button I tried to triger the macro:

Private Sub PrnCo1_Click()

Module5.A1PrintPreviewFlagY

End Sub
--------------------------------------------------------
Thank you very much and please please help!

Zabrina from the SF BayARea
Just another humble analyst & VBA newbie~

 

[PHV]

Isn't the UserForm modal ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Zathras]

PHV is correct.

The solution is to hide the form before leaving it:

Private Sub CommandButton1_Click()
  Me.Hide
  A1PrintPreviewFlagY
End Sub

While I'm at it, I would be remiss if I didn't offer the following variation to your main routine:

Option Explicit

Sub A1PrintPreviewFlagY()
  Rows("1:1").AutoFilter
  Columns("A:BM").EntireColumn.Hidden = False
  HideColumns "G:I", "M:N", "P", "R", "U:V", _
              "X", "AC:AL", "AN:BC", "BE:BG"
  Rows("1:1").AutoFilter
  Rows("1:1").AutoFilter Field:=2, Criteria1:="Y"
  ActiveSheet.PageSetup.PrintArea = "$A:$BI"
  ActiveWindow.SelectedSheets.PrintPreview
End Sub

Private Sub HideColumns(ParamArray Cols() As Variant)
Dim i As Integer
Dim sCol As String
  For i = 0 To UBound(Cols())
    sCol = Cols(i)
    If InStr(sCol, ":") <= 0 Then
      sCol = sCol + ":" + sCol
    End If
    Range(sCol).EntireColumn.Hidden = True
  Next i
End Sub

Notes:
1. It is rarely necessary to select anything in VBA code.
2. Repetitive code can often be replaced by a subroutine.
3. The Array Parameters technique isn't used often but it is a good fit here. Makes adjusting the code for different columns much easier when the environment changes.

 

[suiliclic]

Thank you Sooooooo much for both of your posting PHV & Zathras. To Zathras, that "Me.Hide" statement works like a charm! Thanks for the advice under the macro too, my codes under "A1PrintPreviewFlagY" was actually some default codes written by the Record Macro function on Excel.


Zabrina from the SF BayARea
Just another humble analyst & VBA newbie~