Userform help with named ranges

Tubig

Board Regular
Joined
Feb 20, 2003
Messages
66
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! :biggrin:
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Just replace:

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

With:

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

HTH,

Smitty
 

Tubig

Board Regular
Joined
Feb 20, 2003
Messages
66
Just replace:

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

With:

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

HTH,

Smitty

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

Tubig

Board Regular
Joined
Feb 20, 2003
Messages
66
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. :oops:

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

Board Regular
Joined
Feb 20, 2003
Messages
66

ADVERTISEMENT

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. :oops:

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 :LOL:

CheckBox1.Caption = "RANGE_WATER_AND_SEWER"

That changes the name.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
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

Board Regular
Joined
Feb 20, 2003
Messages
66

ADVERTISEMENT

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
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! :biggrin:
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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:

Code:
CheckBox1.Caption = "Water and Sewer"

Smitty
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
using Smittys remark, which provides more "userfriendly" captions, you would need this line of code
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,811
Messages
5,544,455
Members
410,612
Latest member
MrACED
Top