Solved: How to pass a CheckBox to a subroutine

[sparafucile1]

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]

Change your procedure declaration to:
[VBA]Sub SwitchCellOnOff(check As MSForms.CheckBox)[/VBA]
and you should be good to go.

[sparafucile1]

That was it! Perfecto!! It's working great now, thanks alot!

Jeff

[Norie]

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]

It is a way, but what is the advantage of that?

[Norie]

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]

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
[vba]
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
[/vba]

With a class module Class1
[vba]
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
[/vba]

[Bob Phillips]

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]

What hardcoding in the class setup are you talking about?

[sparafucile1]

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]

Why do you need that?

You already have a reference to the TopLeftCell.
[vba]
CellInfo.Formula = check.TopLeftCell.Offset(,6).Address
[/vba]
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]

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]

Jeff

Yes use string concatenation.
[vba]

CellInfo.Formula = "=" & check.TopLeftCell.Offset(,6).Address
[/vba]
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.