code won't run from a module

[Geoff]

Hi, I have a workbook which has several combobox objects which for one reason
or another have the code associated with their change events written into the
worksheet object rather than into a module. These change events usually
require the worksheet to be unprotected and then protected again, and the
particular worksheet they are on is hidden when the workbook is first opened.
For some reason this arrangement was causing a run-time error 1004 when
anyone tried to save the workbook. I had an idea that perhaps the fact that
the worksheet was hidden was causing a problem.

So my idea was to take the code for these change events out of the worksheet
object and place it into a new module, in the hope that it would then run
from there. The only problem is that now the comboboxes don't seem to call
the code on changing as they did before. Does anyone know the reason why? My
thinking was that as long as the code referred to a correctly-named object,
the change event would call is no matter where the code was, but that is
clearly not the case.

Here is a sample of one of the change event subs:

Private Sub ComboBox63_Change()

Dim RowCnt As Integer

Sheets("Entry").Unprotect ("Password")

If ComboBox63.Text <> "text1" Then
ComboBox64.Text = ""
ComboBox64.Font.Weight = 1
ComboBox64.BackStyle = 0
ComboBox64.SpecialEffect = 0
ComboBox64.ShowDropButtonWhen = 0
Range("rangename1") = ""
End If

If ComboBox63.Text = "text1" Then
ComboBox64.Text = "$0"
ComboBox64.BackStyle = 1
ComboBox64.SpecialEffect = 2
ComboBox64.ShowDropButtonWhen = 2
Range("rangename1") = "text2"
End If

' Turn type option on or off depending on value in this combobox
If ComboBox63.Text = "text3" Or ComboBox63.Text = "text4" Or
ComboBox63.Text = "text5" Then
Range("rangename2") = ""
Range("rangename3") = ""
another_combo_box.Visible = False
Range("rangename4") = ""
If Cells(88, 1).EntireRow.Hidden = False Then
For RowCnt = 88 To 95
Cells(RowCnt, 1).EntireRow.Hidden = True
Next RowCnt
End If
Else
another_combo_box.Visible = True
Range("rangename5") = ""
Range("rangename4") = "text5"
If Cells(88, 1).EntireRow.Hidden = True Then
For RowCnt = 88 To 95
Cells(RowCnt, 1).EntireRow.Hidden = False
Next RowCnt
End If
End If

If ComboBox63.Text = "text1" And Range("rangename1").Value = "" Then
Range("rangename2") = Range("rangename3").Value
End If

If ComboBox63.Text = "text2" And Range("rangename1").Value = "" Then
Range("rangename2") = Range("rangename6").Value
End If

If ComboBox63.Text = "text3" And Range("rangename1").Value = "" Then
Range("rangename2") = Range("rangename7").Value
End If
Sheets("Entry").Protect ("Password")
End Sub

Sorry the post is long and involved but if anyone can provide any insight I
would be grateful.

Thanks

 

[Chad]

I would make sure the combo box name is accurate and that the code is on the
appropriate worksheet where the combo box is actually located. The module
seems to appropriately reference a combo box as is. Hope it helps.

-Chad

 

[Geoff]

Thanks Chad - yes the code runs ok from the worksheet, but for some reason
this seems to cause a runtime error when the workbook is saved (if the
relevant sheet is hidden, as it should be, at startup). I believe this is the
source of the error because when the code is shifted to a module, the
workbook can be saved with no errors. The only problem with that of course is
the code then won't run - sort of a VBA Catch 22. I think I might just go
back to the drawing board, painful as that might be, and try to start over
with these subs - some of which were inherited anyway from a previous version.

Thanks for your reply

 

[Chad]

If the problem occurs upon open or close of the workbook because the sheet is
hidden, you could always write code for on open and/or on close so that the
sheet is hidden/unhidden automatically when these events occur. I'm not sure
exactly how much you'd have to re-write if you had to start over, but the
thought of "re-doing" anything makes me cringe! Good luck

-Chad