Code Works in Macro But Not Command Button?

[shellp]

Hi

The code below works as a macro but I want the code in a command button on the worksheet instead. I am getting the error 'Method Range of object Worksheet failed' error message at the line: What am I doing wrong? All assistance greatly appreciated.

For Each rngdept In ws.Range(strvalidation).Cells

Private Sub CommandButton1_Click()
Dim strvalidation As String
Dim rngdept As Range
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim strPath As String
Dim site_name As String
Dim msgname As String

Set ws = Sheets("Dept")
Set ws2 = Sheets("General")
site_name = ws.Range("B2").Value
msgname = site_name & " departments printed"

strPath = ws2.Range("B7").Value

Application.ScreenUpdating = False
ws.PageSetup.PrintArea = ""

strvalidation = ws.Range("B4").Validation.Formula1

For Each rngdept In ws.Range(strvalidation).Cells
ws.Range("B4").Value = rngdept.Value

If ws.Range("B4").Value = "Emergency Department" Then
ws.PageSetup.PrintArea = "B5:O36"
Else: ws.PageSetup.PrintArea = "B5:O65"
End If

ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strPath & "Patient Safety_2019_20_" & site_name & "_" & rngdept & ".pdf", _
Quality:=xlQualityStandard, _
includeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Next rngdept


Application.ScreenUpdating = True
MsgBox msgname, vbOKOnly
End Sub

 

[Tom.Jones]

Hi,

I didn't study the VBA code but ...
Because you didn't give too much detail ...

1. This code had to be put directly in the sheet, not in the usual module.
2. The button must be ActiveX.

 

[DanteAmor]

It seems that the variable strvalidation is empty.
After this line:
strvalidation = ws.Range("B4").Validation.Formula1

Put this line and comment on what data appears in the msg.
MsgBox strvalidation

If you effectively have a range or a named range. Then try the following:
strvalidation = Mid(ws.Range("B4").Validation.Formula1, 2)
__________________________________________________________________________________-

If the data is in a named range on the ws2 sheet, then try:

For Each rngdept In ws2.Range(strvalidation).Cells

 

[shellp]

Thanks! Okay the message that shows up for the strvalidation is the data validation source (in this case, Indirect($B$2) because the list of departments is dependent on the selection in B2). Using the mid statement produced an error "run time 424: object required".

 

[DanteAmor]

Indirect($B$2)

It means that you are using named range for the validation list.
In which sheet are these data?

Then change here for the name of the sheet:

For Each rngdept In Sheets("NameSheet").Range(strvalidation).Cells

 

[DanteAmor]

Forget the above, if you are using Indirect, then try the following:

Try this, Change Sheet1 to the name of the sheet where you have the named range data:

  strvalidation = ws.Range("B2").Value
  For Each rngdept In Sheets("Sheet1").Range(strvalidation).Cells

 

[shellp]

Thanks for sticking with me! I assume that the second sample for "Sheet1" was for whatever sheet it really is i.e. the name is Dept but it is Sheet6? No matter though because I tried both suggestions and neither worked. I get the error message "application defined or object defined error".

To clarify, the named range is located on the "General" worksheet but will be different depending on the site selected in B2. The first option named range list is in V3:V9 of the Generals tab and the second option named range list is in W3:W8.

 

[DanteAmor]

What do you have in B2?

If in B2 you have the named range

Try the following, but in the end you should put .value:

strvalidation = ws.Range("B2").Value
  For Each rngdept In ws2.Range(strvalidation).Cells

 

[shellp]

Thanks....but back to the 'Method Range of object Worksheet failed' error. Why would it work as is if a macro but not now in the command button?

 

[DanteAmor]

Thanks....but back to the 'Method Range of object Worksheet failed' error. Why would it work as is if a macro but not now in the command button?

Did you modify your code with my suggestion from post #8?
Can you put the complete code here with the changes?

I don't know the reason.
The macro works for me with button inside the sheet and in macro inside a module.

Can you put what you have in cells B2 and B4?
If you have formula in B2 and B4 what formula do you have?
And what values does B2 and B4 have when executing the macro?