Userform help with named ranges

[Tubig]

Hello all,

I was wondering if someone could help me change the code below to print the named ranges (within my workbook) instead of the sheets as shown below:

Private Sub CheckBox1_Click()

End Sub

Private Sub CommandButton1_Click()
If CheckBox1.Value = True Then Sheets("Sheet1").PrintOut Copies:=1
If CheckBox2.Value = True Then Sheets("Sheet2").PrintOut Copies:=1
If CheckBox3.Value = True Then Sheets("Sheet3").PrintOut Copies:=1
If CheckBox4.Value = True Then Sheets("Sheet4").PrintOut Copies:=1
Unload UserForm1

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
CheckBox1.Caption = Sheets("Sheet1").Name
CheckBox2.Caption = Sheets("Sheet2").Name
CheckBox3.Caption = Sheets("Sheet3").Name
CheckBox4.Caption = Sheets("Sheet4").Name

End Sub

Any help would be greatly appreciated!

 

[Smitty]

Just replace:

Sheets("Sheet1").PrintOut Copies:=1

With:

Range("RangeName").PrintOut Copies:=1

HTH,

Smitty

 

[Tubig]

Just replace:

Sheets("Sheet1").PrintOut Copies:=1

With:

Range("RangeName").PrintOut Copies:=1

HTH,

Smitty

AWESOME! Thanks for the quick reply! I'll give it a try.

 

[Tubig]

That works great!

One more question though, how do you change the Userform text to display my range name instead of "Sheet1" etc. I can't seem to figure that part out. I tried everything that I can think of.

Private Sub CheckBox1_Click()

End Sub

Private Sub CommandButton1_Click()
If CheckBox1.Value = True Then Range("RANGE_WATER_AND_SEWER").PrintOut Copies:=1
If CheckBox2.Value = True Then Range("RANGE_ELECTRICAL_SERVICE").PrintOut Copies:=1
If CheckBox3.Value = True Then Range("RANGE_DRILL_PILES").PrintOut Copies:=1
If CheckBox4.Value = True Then Range("RANGE_CRIBBING_MATERIAL").PrintOut Copies:=1
Unload UserForm1

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
CheckBox1.Caption = Sheets("Sheet1").Name
CheckBox2.Caption = Sheets("Sheet2").Name
CheckBox3.Caption = Sheets("Sheet3").Name
CheckBox4.Caption = Sheets("Sheet4").Name

End Sub

 

[Tubig]

That works great!

One more question though, how do you change the Userform text to display my range name instead of "Sheet1" etc. I can't seem to figure that part out. I tried everything that I can think of.

Private Sub CheckBox1_Click()

End Sub

Private Sub CommandButton1_Click()
If CheckBox1.Value = True Then Range("RANGE_WATER_AND_SEWER").PrintOut Copies:=1
If CheckBox2.Value = True Then Range("RANGE_ELECTRICAL_SERVICE").PrintOut Copies:=1
If CheckBox3.Value = True Then Range("RANGE_DRILL_PILES").PrintOut Copies:=1
If CheckBox4.Value = True Then Range("RANGE_CRIBBING_MATERIAL").PrintOut Copies:=1
Unload UserForm1

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
CheckBox1.Caption = Sheets("Sheet1").Name
CheckBox2.Caption = Sheets("Sheet2").Name
CheckBox3.Caption = Sheets("Sheet3").Name
CheckBox4.Caption = Sheets("Sheet4").Name

End Sub

I finally figured it out

CheckBox1.Caption = "RANGE_WATER_AND_SEWER"

That changes the name.

 

[erik.van.geit]

Hello,

assuming your checkboxes captions are named ranges, you can use this code
if you want to add checkboxes afterwards, you won't need to change your code

Private Sub CommandButton1_Click()
Dim ctrl As MSForms.Control

    For Each ctrl In Me.Controls
        If LCase(Left(ctrl.Name, 8)) = "checkbox" Then
        If ctrl Then Range(ctrl.Caption).PrintOut Copies:=1
        End If
    Next ctrl
Unload UserForm1

End Sub

kind regards,
Erik

 

[Tubig]

Hello,

assuming your checkboxes captions are named ranges, you can use this code
if you want to add checkboxes afterwards, you won't need to change your code

Private Sub CommandButton1_Click()
Dim ctrl As MSForms.Control

    For Each ctrl In Me.Controls
        If LCase(Left(ctrl.Name, 8)) = "checkbox" Then
        If ctrl Then Range(ctrl.Caption).PrintOut Copies:=1
        End If
    Next ctrl
Unload UserForm1

End Sub

kind regards,
Erik

Thanks Eric! I'll try that too.

Kindest regards!

 

[Smitty]

how do you change the Userform text to display my range name instead of "Sheet1" etc

You don't necessarily have to name them exactly like your named range:

CheckBox1.Caption = "Water and Sewer"

Smitty

 

[erik.van.geit]

using Smittys remark, which provides more "userfriendly" captions, you would need this line of code

        If ctrl Then Range("Range_" & Application.Substitute(ctrl.Caption, " ", "_")).PrintOut Copies:=1

basically you need to add "range_" and to replace any space by an underscore

best regards,
Erik