userform checkbox to determine which portion of code to run

[baker_89]

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

 

[My Aswer Is This]

Assuming your check box name is: "mon"
Your script should look like this:

If mon.Value = True Then
Sheets("Sheet1").UsedRange.ClearContents
Sheets("Sheet2").UsedRange.ClearContents

This would clear the entire contents of the sheet

 

[mikerickson]

You say that the OP code "doesn't work right". It looks like it should. What is wrong about what the OP code does?

 

[baker_89]

Compile Error:

Block If without End If

 

[mikerickson]

The syntax should be something like

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]

The syntax should be something like

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]

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

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]

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]

@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

' 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.

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

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

 

[My Aswer Is This]

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?