• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA will run from module but not when called in userform

Gman87

New Member
Hello All,

I have been working on creating cascading drop down/combo boxes in a userform.
the user selects a "Zone" then a "Style" then selects a "Model"

I have a macro that creates a unique list of 'styles' based on the zone selected, and then a separate macro to create a unique list of 'Models" based on the 'Style selected'. these macros work great when i run them from a module, but when I try to run them from the userform combo box change event i get errors.
 
In the userform I just use 'Call Style_Filter' from userform initialize and 'call Model_Filter' from a combo box change event

Here is the code I am running in the module

Code:
Sub Style_Filter()

Application.ScreenUpdating = False
Worksheets("Lists").Activate
  Range("S5:T500").Clear
  Range("S4").Clear
  ActiveSheet.ListObjects("Current_Styles").Resize Range("$S$3:$T$4")
  
  Range("V5:W500").Clear
  Range("V4").Clear
  ActiveSheet.ListObjects("Current_Models").Resize Range("$V$3:$W$4")


ActiveSheet.ListObjects("ModelMaster").Range.AutoFilter Field:=1, Criteria1:=Range("M1")
Range("m3").Activate
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2).Select
Range(Selection, Selection.End(xlDown)).Copy
Range("S4").PasteSpecial xlPasteValues
ActiveSheet.Range("Current_Styles[#All]").RemoveDuplicates Columns:=1, Header:=xlYes
ActiveSheet.ListObjects("ModelMaster").Range.AutoFilter Field:=1

Worksheets("Home").Activate
Application.ScreenUpdating = True
End Sub


Sub Model_Filter()


Application.ScreenUpdating = False
Worksheets("Lists").Activate

  Range("V5:W500").Clear
  Range("V4").Clear
  ActiveSheet.ListObjects("Current_Models").Resize Range("$V$3:$W$4")

ActiveSheet.ListObjects("ModelMaster").Range.AutoFilter Field:=1, Criteria1:=Range("M1")
ActiveSheet.ListObjects("ModelMaster").Range.AutoFilter Field:=2, Criteria1:=Range("N1")
Range("m3").Activate
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 4).Select
Range(Selection, Selection.End(xlDown)).Copy
Range("V4").PasteSpecial xlPasteValues
ActiveSheet.ListObjects("ModelMaster").Range.AutoFilter Field:=2
ActiveSheet.ListObjects("ModelMaster").Range.AutoFilter Field:=1

Worksheets("Home").Activate
Application.ScreenUpdating = True

End Sub



Any help is much Appreciated!!!
 
Last edited by a moderator:
Please use code tag when posting VBA (or any codes).
upload_2017-8-18_13-22-31.png

Since you are calling on the sub from Active userform... you should take care to define context of each object.
Ex. This portion of your code...
Code:
Worksheets("Lists").Activate

Range("V5:W500").Clear
Range("V4").Clear
ActiveSheet.ListObjects("Current_Models").Resize Range("$V$3:$W$4")

Should be changed to....
Code:
With Worksheets("Lists")
    .Range("V5:W500").Clear
    .Range("V4").Clear
    .ListObjects("Current_Models").Resize .Range("$V$3:$W$4")
'same for rest of your code
End With

It would help you get more specific answer, if you can highlight the line that gives error message and what error code is raised.
 
Thanks I missed the code tag! sorry, I have made the necessary adjustments and will do that in the future coding projects.

I am getting clear method of range class error at
.Range("V5:W500").Clear

I can run the code from the module with no issues but once I run it from the user form i get the error, after that i will get the same errors when trying in the module.

Thanks Again,

Code:
Sub Model_Filter()


Application.ScreenUpdating = False
With Worksheets("Lists")

    .Range("V5:W500").Clear
    .Range("V4").Clear
    .ListObjects("Current_Models").Resize Range("$V$3:$W$4")

.ListObjects("ModelMaster").Range.AutoFilter Field:=1, Criteria1:=Range("M1")
.ListObjects("ModelMaster").Range.AutoFilter Field:=2, Criteria1:=Range("N1")
.Range("m3").Activate
.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 4).Select
.Range(Selection, Selection.End(xlDown)).Copy
.Range("V4").PasteSpecial xlPasteValues
.ListObjects("ModelMaster").Range.AutoFilter Field:=2
.ListObjects("ModelMaster").Range.AutoFilter Field:=1
End With
Worksheets("Home").Activate
Application.ScreenUpdating = True

End Sub
 
Can you upload sample workbook with desensitized data?

I tested on my end without issue, using mock up sheet of random data and calling Range.Clear routine from userform using Call Sub_Name.

Below in standard module (i.e. Module1).
Code:
Sub Demo()
With Worksheets("Sheet2")
    .Range("V5:V500").Clear
    .Range("V4").Clear
End With
End Sub

Just as test I had on Userform_Click event
Code:
Private Sub UserForm_Click()
    Call Demo
End Sub
 
I can, it will take me a little while to do it though. If I recall last time I got stuck on an issue I rebuilt that to post it and in doing so the issue dissapeared, so maybe with any luck in the desensitization process I learn from my own mistake! I will post back when I can get it completed. Thanks!
 
Well, in building a desensitized test sheet the problem went away...not sure why though, I feel it is something to do with the sheet formatting. Thank you for you help and tip on using the With Command!
 
Back
Top