userform checkbox to determine which portion of code to run

baker_89

New Member
Joined
Aug 25, 2014
Messages
42
I have a userform with 6 checkboxs, all, mon, tue, wed, thur, fri, sat. I want the user to be able to pick which date(sheet) to choose to run certain code on. This is what I have but it doesn't work right.

Private Sub CommandButton1_Click()




If all.Value = True Then
Clear Ranges in all sheets
Else

If mon.Value = True Then
'Clear Ranges on Sheets 1 & 2

If tue.Value = True Then
'Clear Ranges on Sheets 3 & 4
Else

If thur.Value = True Then
'Clear Ranges on Sheets 5 & 6
Else

If fri.Value = True Then
'Clear Ranges on Sheets 7 & 8
Else

If sat.Value = True Then
'Clear Ranges on Sheets 1 & 2
Else

End If

End Sub
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,256
Office Version
  1. 2013
Platform
  1. Windows
Assuming your check box name is: "mon"
Your script should look like this:
Code:
If mon.Value = True Then
Sheets("Sheet1").UsedRange.ClearContents
Sheets("Sheet2").UsedRange.ClearContents
This would clear the entire contents of the sheet
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
You say that the OP code "doesn't work right". It looks like it should. What is wrong about what the OP code does?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916

ADVERTISEMENT

The syntax should be something like
Code:
If all.Value Then
    Clear Ranges in all sheets
ElseIf mon.Value Then
    'Clear Ranges on Sheets 1 & 2
ElseIf tue.Value Then
    'Clear Ranges on Sheets 3 & 4
ElseIf thur.Value Then
    'Clear Ranges on Sheets 5 & 6
ElseIf fri.Value Then
    'Clear Ranges on Sheets 7 & 8
ElseIf sat.Value Then
    'Clear Ranges on Sheets 1 & 2
End If

I'm also wondering if OptionButtons might be a better choice than Checkboxes
 

baker_89

New Member
Joined
Aug 25, 2014
Messages
42
The syntax should be something like
Code:
If all.Value Then
    Clear Ranges in all sheets
ElseIf mon.Value Then
    'Clear Ranges on Sheets 1 & 2
ElseIf tue.Value Then
    'Clear Ranges on Sheets 3 & 4
ElseIf thur.Value Then
    'Clear Ranges on Sheets 5 & 6
ElseIf fri.Value Then
    'Clear Ranges on Sheets 7 & 8
ElseIf sat.Value Then
    'Clear Ranges on Sheets 1 & 2
End If

I'm also wondering if OptionButtons might be a better choice than Checkboxes


That did it! Works perfect thank you!

I did try OptionButtons but it didn't allow multiple selections.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916

ADVERTISEMENT

The code that I posted doesn't react to multiple selections, it executes the code for the first checkbox that tests True and ignores the rest.
If you want multiple responses, code like this

Code:
If Mon.Value Then 
    'Clear Ranges on Sheets 1 & 2
End If

If Tue.Value Then
    'Clear Ranges on Sheets 3 & 4
End If
' etc.

If All.Value Then
    'Clear Ranges in all sheets
End If

This presumes that the All routine doesn't interfere with any other day's code.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,256
Office Version
  1. 2013
Platform
  1. Windows
This is something new to me. I did not know you could clear sheets like this. I have always thought you needed to use commands like I posted in post # 2
Please explain what this code is doing.
When I put the command: Clear Ranges in all sheets in a module and try and use it does not work.
I get a syntax error
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
@My Answer,
Those lines aren't code, they are comments. Nothing is excited they are notes about what is happening. The code you posted is a proper syntax for clearing the cells in a sheet.

Comments can be done two ways:
1) precede the comment with a single apostrophe as in the code above
Code:
' this is a comment

2) (old school) start the line with a Rem statement. I prefer this, since the apostrophe is easy to miss, but I'm odd.
Code:
Rem this is a comment

The difference is that the apostrophe can be anywhere in the line, the REM must be at the start of the line

Code:
boxLength = Range("A1").Value ' get length from A1

' vs

Rem get length from A1
boxLength = Range("A1").Value

' this errors
boxLength = Range("A1").Value Rem get length from A1

'this does not (note the line separator : )
boxLength = Range("A1").Value: Rem get length from A1
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,256
Office Version
  1. 2013
Platform
  1. Windows
Mike. I saw the ' marks and do understand they are normally used for comments.
The first command:
If all.Value = True Then
Clear Ranges in all sheets
Else
has no ' at the beginning of the line.
So if none of this is code then what is the purpose of all this?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,085
Messages
5,599,650
Members
414,325
Latest member
kfg1287

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
Top