Excel VBA UserForm, Checkbox's to print sheets & Save as new workbook

atame

New Member
Joined
May 26, 2015
Messages
31
I am in need of a bit of assistance, i am trying to create a UserFrom that creates checkbox's based on sheet names. It will then have 4 button, 1:Print, 2:Save New Workbook, 4:Clear Selection, 5:Cancel.
This is the code i have that creates the checboes, and changes the captions to the sheet names.

Code:
Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim i As Integer
    
    For i = 1 To Worksheets.Count
    Set ws = Worksheets(i)
    Set Lbl = Frame1.Controls.Add("Forms.Checkbox.1", "Lbl1" & i, True)
    Lbl.Caption = ws.Name
    Lbl.Top = offset
    offset = offset + 15
    Next i
End Sub

This is the code that clears the selections.

Code:
Private Sub CommandButton2_Click()
Dim ctrl As Control


For Each ctrl In UserForm1.Controls
    If TypeName(ctrl) = "CheckBox" Then
        ctrl.Value = False
    End If
Next
End Sub

I need help with buttons 1 & 2, print the sheets and saving as a new workbook. When it prints it need to have the same printing formatting as if i were printing normally.
The newly created workbook will need to be save with the naming convention below

Code:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#000000]NewWorkbook[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]SaveAs Filename[/COLOR][COLOR=#000000]:=[/COLOR][COLOR=#000000]MyPath [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]"\"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]"Remittance"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]" "[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] MyRange[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Value [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]" "[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] MyDate[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Text [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]".xls"[/COLOR][COLOR=#808080]'This saves a new document in our naming convention[/COLOR]</code>

Any help you can provide will be greatly appreciated.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,215,646
Messages
6,126,004
Members
449,279
Latest member
Faraz5023

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top