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