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

 

[shellp]

When I run the macro, I added the msgbox line and get the same value: Indirect($B$2).

On the worksheet Dept in B2 is a drop down list to select "hospital_A" or "hospital_B". B4 has the department value but the data_validation list source for B4 is indirect($B$2) because there is a list of departments with the named range of "hospital_A" and a different list for "hospital_B". Using indirect allows me to control the department names showing up depending on the hospital chosen.

So no formulae in the cells of B2 or B4 just cells that both have data validation where the source is a named range. B2's source is "sites" and as mentioned above, B4 is indirect($B$2) and is based on the site chosen.

In #8 you indicated

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

but did you mean ws not ws2?

With the code as below, I still get the error message 'Method Range of object Worksheet failed' .

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").Value

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

 

[DanteAmor]

Change this:

strvalidation = ws.Range("[COLOR=rgb(226, 80, 65)][B]B4[/B][/COLOR]").Value
For Each rngdept In [COLOR=rgb(226, 80, 65)][B]ws.[/B][/COLOR]Range(strvalidation).Cells

For this:

  strvalidation = ws.Range("[B][COLOR=rgb(65, 168, 95)]B2[/COLOR][/B]").Value
  For Each rngdept In [B][COLOR=rgb(65, 168, 95)]ws2[/COLOR][/B].Range(strvalidation).Cells

It looks strange, but it works and there is an explanation:
In B2 you have: "hospital_A", this is the named range, and the data or range of that named range is on the ws2 sheet.

Try and comment.

 

[shellp]

Did you mean to have references to colors because that wasn't in the original?

 

[DanteAmor]

Did you mean to have references to colors because that wasn't in the original?

I'm sorry, it should be like this:
Change your code:

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

For this:

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

It looks strange, but it works and there is an explanation:
In B2 you have: "hospital_A", this is the named range, and the data or range of that named range is on the ws2 sheet.

Try and comment.

 

[shellp]

I figured out you didn't mean to include the color information and TA DA....your suggestion, without the color stuff, worked!! Thank you so very much!!

 

[DanteAmor]

? I'm glad to help you. Thanks for the feedback.