VBA Checkboxes

[KristelSmith]

I'm using Excel 2007, but I want the code to be capable of being run
in 2003.
I have five Activex checkboxes and a non activex "go" button on an
excel sheet.
The "go" button is set to run what is in module 1. This part is
working.

What I want to do is setup 5 "if ... then" statements that are
independent of each other. I want the module 1 sub to do what it's
set to do (all working) and THEN evaluate whether a checkbox is true
or not. If it is true, I would like it to call the appropriate code
and run it.

So, for the first checkbox named work, I have put the code in a class
of it's own named class1 for now (not sure if that's where it
belongs).

What do I need to put in module 1(and declare) to evaluate the "work"
checkbox and call "class1" if the value is true and if false just move
onto the next checkbox?

In shorthand... under module 1, I want to do something like:

If work checkbox = true then run the class1 code
if next checkbox = true then run the class2 code

The code sitting in class1 works just fine when I have it under module
1 and no checkbox is being evaluated.
I can move it wherever it belongs, just not sure where that might
be.

The reason I want to call the code instead of adding it to the if then
statement is really because it's really long and I want to seperate it
for ease of adding to it later. Either way, I can't seem to get it to
evaluate a checkbox from the module... or even the worksheet when I
moved the code over there.

If this isn't clear, let me know.

 

[Dave Peterson]

You can use code like this that will check the value the checkboxes--and they're
really named Next and Work???

If ActiveSheet.OLEObjects("Next").Object.Value = True then
'it's checked.

I'm not sure what you're doing, but I'm not sure what code you'd be using that
would be long in a class module.

 

[KristelSmith]

I have code setup to make a copy of a base workbook and save it to the
user's "my documents" directory. (works)
Then I have several workbooks setup with information on them. The
sheetnames match, although the base file has more worksheets than the
rest of the sheets.

We'll call the base sheet "main" and an extra sheets "work" and
"sheet"
Currently, I am able to copy all of the information from "work" and
"sheet" to the next available rows on the matching sheet names in
"main." This is great, except sometimes you only need one or the other
and sometimes you need both.

All of the code is currently sitting in Module 1.

So I decided to just add checkboxes on a worksheet named "form" with a
go button. What I would like to do is have them select whichever they
want to copy over... hit the go button and have it copy over the
appropriate information.

My go button is calling module 1 just fine... it goes through the
steps that always needs to happen but never makes it to determining
whether a checkbox is checked and running the appropriate code.

Where I'm having trouble:
I'm not sure where to put the code that does the copying
I'm not sure how to call that code from module 1 when the checkbox is
checked
I don't think I'm calling the correct object... as in the activex
checkbox. I'm not committed to activex, it just seemed to give me the
most ability

I don't think posting my code will help... but I'm just not seeing it
documented anywhere.

 

[Chris]

Your code should be in Module1 with the other code in a seperate routine.
Also, one way of doing your code is with Select Case. All of your check
boxes would need to be named and grouped. However, if you are using
checkboxes, then there is the possibility more than one has been checked. If
you are going for exclusivity of choice, then you may want to consider radio
buttons.

 

[Dave Peterson]

First some house keeping.

Remove the checkboxes and commandbutton from the Control toolbox.

From the Forms toolbar, add a button and 2 checkboxes. I put them in row 1 and
used window|freeze panes so that row 1 is always visible.

I put the checkboxes and button on the Main sheet. I would think it would make
it easier for user than to go to a different worksheet.

Name and label the 2 checkboxes nicely.
Select the first (rightclicking on it is easy)
Type Sheet in the namebox (to the left of the formulabar) and hit enter
Change the caption to "Copy to Sheet"

Select the second (rightclicking on it is easy)
Type Work in the namebox and hit enter
Change the caption to "Copy to Work"

Add this code to a General module.

Option Explicit
Sub DoTheCopy()

Dim CBXSheet As CheckBox
Dim CBXWork As CheckBox
Dim WksSheet As Worksheet
Dim WksWork As Worksheet
Dim RngToCopy As Range
Dim myCell As Range
Dim DestCell As Range

Set WksSheet = Worksheets("Sheet")
Set WksWork = Worksheets("Work")

If Selection.Areas.Count > 1 Then
MsgBox "Please select a single area!"
Exit Sub
End If

With ActiveSheet
Set RngToCopy = Nothing
On Error Resume Next
'just look at column A
Set RngToCopy = Intersect(Selection.EntireRow, .Columns(1), .UsedRange)
On Error GoTo 0
If RngToCopy Is Nothing Then
MsgBox "Nothing to copy!"
Exit Sub
End If

Set CBXSheet = .CheckBoxes("Sheet")
Set CBXWork = .CheckBoxes("Work")

If CBXWork.Value = xlOff _
And CBXSheet.Value = xlOff Then
MsgBox "Please check one of the boxes"
Exit Sub
End If

For Each myCell In RngToCopy.Cells
If myCell.Value = "" Then
'skip it
Else
If CBXSheet.Value = xlOn Then
With WksSheet
Set DestCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
myCell.EntireRow.Copy _
Destination:=DestCell
End If
If CBXWork.Value = xlOn Then
With WksWork
Set DestCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
myCell.EntireRow.Copy _
Destination:=DestCell
End If
End If
Next myCell

'stop from hitting the button twice
'by turning the checkboxes off
CBXSheet.Value = xlOff
CBXWork.Value = xlOff

End With

End Sub

Rightclick on the button and choose assign macro. Assign this macro to the
button.

The code allows you to to select a single range and every row that has a value
in column A of that row is copied to the Work and/or Sheet worksheet.

You may want to use a different column than A, but it'll be easier if you can
pick out a column that always has data if that row is used.

 

[KristelSmith]

Thank you so much.

So the code you wrote would go into my "Module 1" and then I associate
the checkbox with the module... or do I create a new module (or class
or whatever...) for this code and put the if then statements in module
1 and associate the checkbox to the module?

Also, will this code wait until the go button is clicked or will it
act as soon as the box is checked? I want it to wait until go is
clicked...

 

[Dave Peterson]

The code would go in a general module (Module1, Module2, ...). I'm not sure if
there's anything in module1 that you want to keep. If there isn't, then you can
delete all the code and place it there.

But don't put it in any of the Class, worksheet, thisworkbook modules.

And the code gets assigned not to the checkbox. Assign it to the button.

There is no code assigned to either checkbox.