PDA

View Full Version : Solved: How to pass a CheckBox to a subroutine



sparafucile1
11-08-2007, 02:09 PM
Hello everyone.

I'm new to VBA but a lifelong C programmer. I'm trying to make a subroutine that you can accept a CheckBox argument. What I have is a piece of code for a CheckBox_Click() sub that I want to be able to re-use for anothe 30 - 40 sub's.

The dumb way is to manually copy them and I don't want to do that. I thought just creating a subroutine which did the guts of what I wanted and passing in a pointer (sorry can't give up C) to the CheckBox.

Here is some code:



Private Sub CheckBox1_Click()

'Call the subroutine (Type mismatch occurs here!!)
SwitchCellOnOff CheckBox1

End Sub


Private Sub CheckBox2_Click()

'Call the subroutine (Type mismatch occurs here!!)
SwitchCellOnOff CheckBox2

End Sub

'Eventually there'll be 30 - 40 more of these here...




'My function that I want duplicated for every Click()
Sub SwitchCellOnOff(check As CheckBox)

Dim RownNum As Integer
Dim ColNum As Integer
Dim CellInfo As Range
Dim Cycles As Integer

If check Then

' Find the Column and row for this checkbox
RowNum = check.TopLeftCell.Row
ColNum = check.TopLeftCell.Column

' If enabled we should be turning 'ON' the font
Set CellInfo = ActiveSheet.Cells(RowNum, ColNum + 1)
CellInfo.Font.ColorIndex = 1
Set CellInfo = ActiveSheet.Cells(RowNum, ColNum + 2)
CellInfo.Font.ColorIndex = 1
Set CellInfo = ActiveSheet.Cells(RowNum, ColNum + 3)
CellInfo.Font.ColorIndex = 1
Set CellInfo = ActiveSheet.Cells(RowNum, ColNum + 4)
CellInfo.Font.ColorIndex = 1

' Copy the cell count into the "In Project" Column
Cycles = CellInfo.Value
Set CellInfo = ActiveSheet.Cells(RowNum, ColNum + 6)
CellInfo.Value = Cycles


Else

' Find the Column and row for this checkbox
RowNum = check.TopLeftCell.Row
ColNum = check.TopLeftCell.Column

' If disabled we should be turning 'OFF' the font
Set CellInfo = ActiveSheet.Cells(RowNum, ColNum + 1)
CellInfo.Font.ColorIndex = 15
Set CellInfo = ActiveSheet.Cells(RowNum, ColNum + 2)
CellInfo.Font.ColorIndex = 15
Set CellInfo = ActiveSheet.Cells(RowNum, ColNum + 3)
CellInfo.Font.ColorIndex = 15
Set CellInfo = ActiveSheet.Cells(RowNum, ColNum + 4)
CellInfo.Font.ColorIndex = 15

' Remove Copy the cycle count from the "In Project" Column
Set CellInfo = ActiveSheet.Cells(RowNum, ColNum + 6)
CellInfo.ClearContents

End If

End Sub



I get a type mismatch with this code when I try to call it from the Click() sub. Any ideas how to make this work?

Thanks.
Jeff

rory
11-08-2007, 04:25 PM
Change your procedure declaration to:
Sub SwitchCellOnOff(check As MSForms.CheckBox)
and you should be good to go.

sparafucile1
11-09-2007, 07:50 AM
That was it! Perfecto!! It's working great now, thanks alot!
:beerchug:
Jeff

Norie
11-09-2007, 08:53 AM
Couldn't this be handled using a class module rather than having a sub for each checkbox even if all that sub is doing is calling another.

Bob Phillips
11-09-2007, 09:00 AM
It is a way, but what is the advantage of that?

Norie
11-09-2007, 09:17 AM
Well you wouldn't need 40 subs.:)

It could also work dynamically.

eg the OP says 30-40 checkboxes, perhaps that means the number might change.

Also you wouldn't need to hardcode the names of the checkboxes.

Mind you all of these 'advantages' and how to implement the idea would depend on what the OP is actually trying to do and where these checkboxes are located.

Since all it seems they want to do is change some formatting I would actually suggest they take another approach. eg conditional formatting.

Andy Pope
11-09-2007, 09:30 AM
Hi guys,

The class approach would only require a small amount of code to build the collection holding the references to each checkbox which runs once.
And no need to add code in each checkbox event.

In a code module

Public g_colCheckboxes As Collection

Sub BuildEventHandler()

Dim objTemp As OLEObject
Dim clsTemp As Class1

Set g_colCheckboxes = New Collection
For Each objTemp In Sheet1.OLEObjects
If TypeName(objTemp.Object) = "CheckBox" Then
Set clsTemp = New Class1
Set clsTemp.MyCheckBox = objTemp.Object
g_colCheckboxes.Add clsTemp, clsTemp.MyCheckBox.Name
End If
Next

End Sub


With a class module Class1

Option Explicit

Public WithEvents MyCheckBox As MSForms.CheckBox


Private Sub MyCheckBox_Click()

Dim RowNum As Integer
Dim ColNum As Integer
Dim lngFontColorIndex As Long

' Find the Column and row for this checkbox
RowNum = Me.MyCheckBox.TopLeftCell.Row
ColNum = Me.MyCheckBox.TopLeftCell.Column

If Me.MyCheckBox.Value Then
' If enabled we should be turning 'ON' the font
lngFontColorIndex = 1

' Copy the cell count into the "In Project" Column
ActiveSheet.Cells(RowNum, ColNum + 6) = ActiveSheet.Cells(RowNum, ColNum + 4)

Else
' If disabled we should be turning 'OFF' the font
lngFontColorIndex = 15

' Remove Copy the cycle count from the "In Project" Column
ActiveSheet.Cells(RowNum, ColNum + 6).ClearContents

End If

ActiveSheet.Cells(RowNum, ColNum + 1).Resize(1, 4).Font.ColorIndex = lngFontColorIndex

End Sub

Bob Phillips
11-09-2007, 09:35 AM
40 subs - nothing. Click on the checkbox and it gives you a template. All you do is fill in the call. No big deal.

Adding another - come on that is no big deal. Has to remember to do it agreed, but you woulkd have to pass the to the calss so that the calss monitors it anyway.

Hardcoding - yes you would, in the calss setup.

Efficiency - worse, much worse. The subroutine will beta it any day.

Maintainability. Worse. You have an obscure class module, and some obscure setup code using class modules. Uisng a subroutine, it would all be in the form module, very simple to understand and use.

All in all, the class approach doesn't seem to add any advantages to the OP that I can see.

Norie
11-09-2007, 09:50 AM
What hardcoding in the class setup are you talking about?

sparafucile1
11-09-2007, 11:36 AM
Hi guys,

Wow I didn't expect so many replies after I had something working! Let me start by saying, yes it would be wonderful if I didn't have to copy the sub call in each Click().

All checkboxes will behave in exactly the same way, so it would be nice if someone else who was using the spreadsheet could simply copy the checkbox object and not have to know about adding code to VB script.

While I'm posting here I have another dumb/simple question. Let's same I have a rol and column number for a cell, but I want it to convert into the standard LetterNumber convention...

i.e. I have row 3, Col2 and I want this to mean "B3"... How would I do this. Essentially I looking for the equivalent to this:



Dim FormulaString As String

'Points to "C3"
RowNum = check.TopLeftCell.Row
ColNum = check.TopLeftCell.Column

Set CellInfo = ActiveSheet.Cells(RowNum, ColNum + 6)

'I would like this string to be built using RowNum,ColNum!!!
FormulaString = "=C3"
CellInfo.Formula = FormulaString




Any ideas?

Norie
11-09-2007, 11:56 AM
Why do you need that?

You already have a reference to the TopLeftCell.

CellInfo.Formula = check.TopLeftCell.Offset(,6).Address

By the way the class module idea wouldn't actually be totally dynamic.

What I mean is there isn't any event triggered when a control is added, so there's no way to add the newly created checkbox to the class.

Also do you actually need these checkboxes?

sparafucile1
11-09-2007, 12:20 PM
Yep, I do need them. I need some type of graphical button or checkbox so that a user can pick from a list and when they are picked the number associated in this row can be tallied up.

In this way I can have a "laundry list" of items and they just have to select what they want from the list and *poof* they get the total count of the objects that they selected.

BTW, using the code above set the string "$C$3" in the cell and not the "=$C$3" that I expected. Should I just use simple strin manipulations to pre-pend the "=" to?

Thanks,
Jeff

Norie
11-09-2007, 12:28 PM
Jeff

Yes use string concatenation.


CellInfo.Formula = "=" & check.TopLeftCell.Offset(,6).Address

Bit simpler than C (or was it C++) if I recall but that was some eons ago.

PS I still think you might be able to achieve what you seem to want to do without either checkboxes or code.

Well maybe you would need a little code.