Greetings Fellow Tek-Tippers,
I have a (hopefully) simple question of the group concerning Worksheet Protection and Control Objects.
I have developed a calculator of sorts for our staff to use, where a driver Combo-Box will set the Visibility, Link Cell, Cell Link Range of other Combo-Boxes based on the choice made.
Everything is working fine from a functionality standpoint, but my issue arose when I protected the worksheet (the sheet needs to be locked so formula cells cannot be edited). Once I protected the worksheet, an exception is generated when I "x" (close) out of Excel. If I "x" (close) out of the document within Excel, this exception is not thrown.
I assume, as my VBA is on the "_Change" of the Combo-Box, that Excel is trying to access this VBA upon exit, but as the application is closed, it is no longer accessible.
My question to the group is: any thoughts on ideas to keep the protection on the worksheet and not have this exception generated upon exitting MS Excel?
The exact error is:
Compile error:
Method or data member not found
the compiler then highlights the object name (CmbBox_Dynamic1) within first line of the if statment in the "_Change" subroutine.
The VBA Code for this worksheet is as follows:
Thanks in advance for all your assistance.
Cheers!
Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."
I have a (hopefully) simple question of the group concerning Worksheet Protection and Control Objects.
I have developed a calculator of sorts for our staff to use, where a driver Combo-Box will set the Visibility, Link Cell, Cell Link Range of other Combo-Boxes based on the choice made.
Everything is working fine from a functionality standpoint, but my issue arose when I protected the worksheet (the sheet needs to be locked so formula cells cannot be edited). Once I protected the worksheet, an exception is generated when I "x" (close) out of Excel. If I "x" (close) out of the document within Excel, this exception is not thrown.
I assume, as my VBA is on the "_Change" of the Combo-Box, that Excel is trying to access this VBA upon exit, but as the application is closed, it is no longer accessible.
My question to the group is: any thoughts on ideas to keep the protection on the worksheet and not have this exception generated upon exitting MS Excel?
The exact error is:
Compile error:
Method or data member not found
the compiler then highlights the object name (CmbBox_Dynamic1) within first line of the if statment in the "_Change" subroutine.
The VBA Code for this worksheet is as follows:
Code:
Private Sub CmbBox_MajorType_Change()
'Set the values of each ComboBox based on the Major Chosen
If Sheet1.CmbBox_MajorType.Value = 1 Then
'Major Type ComboBox = "Please Select" - Hide All Other ComboBoxes
Sheet1.CmbBox_Dynamic1.Visible = False
Sheet1.CmbBox_Dynamic2.Visible = False
Sheet1.CmbBox_Dynamic3.Visible = False
Sheet1.CmbBox_Dynamic4.Visible = False
Sheet1.CmbBox_Dynamic5.Visible = False
Sheet1.CmbBox_Dynamic6.Visible = False
Sheet1.CmbBox_Discount1.Visible = False
Sheet1.CmbBox_Premium1.Visible = False
Sheet1.CmbBox_Volume1.Visible = False
ElseIf Sheet1.CmbBox_MajorType.Value <> 1 Then
'Major Type ComboBox <> "Please Select" - Show All Other ComboBoxes (Except Dynamic #6)
Sheet1.CmbBox_Dynamic1.Visible = True
Sheet1.CmbBox_Dynamic2.Visible = True
Sheet1.CmbBox_Dynamic3.Visible = True
Sheet1.CmbBox_Dynamic4.Visible = True
Sheet1.CmbBox_Dynamic5.Visible = True
Sheet1.CmbBox_Discount1.Visible = True
Sheet1.CmbBox_Premium1.Visible = True
Sheet1.CmbBox_Volume1.Visible = True
'********************************************************************************
'Set the ListFillRange, List Length & LinkCell for all "Set List-Length" Criteria
'********************************************************************************
Sheet1.CmbBox_Dynamic1.ListFillRange = "Criteria!A35:C39"
Sheet1.CmbBox_Dynamic1.ListRows = 5
Sheet1.CmbBox_Dynamic1.LinkedCell = "Criteria!E34"
Sheet1.CmbBox_Dynamic1.Value = 1
Sheet1.CmbBox_Dynamic4.ListFillRange = "Criteria!A70:C73"
Sheet1.CmbBox_Dynamic4.ListRows = 4
Sheet1.CmbBox_Dynamic4.LinkedCell = "Criteria!E69"
Sheet1.CmbBox_Dynamic4.Value = 1
Sheet1.CmbBox_Dynamic5.ListFillRange = "Criteria!A80:C84"
Sheet1.CmbBox_Dynamic5.ListRows = 5
Sheet1.CmbBox_Dynamic5.LinkedCell = "Criteria!E79"
Sheet1.CmbBox_Dynamic5.Value = 1
Sheet1.CmbBox_Discount1.ListFillRange = "Criteria!A101:C104"
Sheet1.CmbBox_Discount1.ListRows = 4
Sheet1.CmbBox_Discount1.LinkedCell = "Criteria!E100"
Sheet1.CmbBox_Discount1.Value = 1
Sheet1.CmbBox_Premium1.ListFillRange = "Criteria!A111:C113"
Sheet1.CmbBox_Premium1.ListRows = 3
Sheet1.CmbBox_Premium1.LinkedCell = "Criteria!E110"
Sheet1.CmbBox_Premium1.Value = 1
Sheet1.CmbBox_Volume1.ListFillRange = "Criteria!A120:C129"
Sheet1.CmbBox_Volume1.ListRows = 10
Sheet1.CmbBox_Volume1.LinkedCell = "Criteria!E119"
Sheet1.CmbBox_Volume1.Value = 1
'*****************************************************************************************
'Set the ListFillRange, List Length & LinkCell for all criteria with varied number of rows
'*****************************************************************************************
If Sheet1.CmbBox_MajorType.Value = 2 Then
'Major Type ComboBox = "Agriculture"
Sheet1.CmbBox_Dynamic2.ListFillRange = "Criteria!A46:C51"
Sheet1.CmbBox_Dynamic2.ListRows = 6
Sheet1.CmbBox_Dynamic2.LinkedCell = "Criteria!E45"
Sheet1.CmbBox_Dynamic2.Value = 1
Sheet1.CmbBox_Dynamic3.ListFillRange = "Criteria!A58:C63"
Sheet1.CmbBox_Dynamic3.ListRows = 6
Sheet1.CmbBox_Dynamic3.LinkedCell = "Criteria!E57"
Sheet1.CmbBox_Dynamic3.Value = 1
Sheet1.CmbBox_Dynamic6.Visible = False
End If
If Sheet1.CmbBox_MajorType.Value = 3 Then
'Major Type ComboBox = "Commercial"
Sheet1.CmbBox_Dynamic2.ListFillRange = "Criteria!A46:C51"
Sheet1.CmbBox_Dynamic2.ListRows = 6
Sheet1.CmbBox_Dynamic2.LinkedCell = "Criteria!E45"
Sheet1.CmbBox_Dynamic2.Value = 1
Sheet1.CmbBox_Dynamic3.ListFillRange = "Criteria!A58:C63"
Sheet1.CmbBox_Dynamic3.ListRows = 6
Sheet1.CmbBox_Dynamic3.LinkedCell = "Criteria!E57"
Sheet1.CmbBox_Dynamic3.Value = 1
Sheet1.CmbBox_Dynamic6.Visible = False
End If
If Sheet1.CmbBox_MajorType.Value = 4 Then
'Major Type ComboBox = "Consumer"
Sheet1.CmbBox_Dynamic2.ListFillRange = "Criteria!A46:C50"
Sheet1.CmbBox_Dynamic2.ListRows = 5
Sheet1.CmbBox_Dynamic2.LinkedCell = "Criteria!E45"
Sheet1.CmbBox_Dynamic2.Value = 1
Sheet1.CmbBox_Dynamic3.ListFillRange = "Criteria!A58:C62"
Sheet1.CmbBox_Dynamic3.ListRows = 5
Sheet1.CmbBox_Dynamic3.LinkedCell = "Criteria!E57"
Sheet1.CmbBox_Dynamic3.Value = 1
Sheet1.CmbBox_Dynamic6.Visible = True
Sheet1.CmbBox_Dynamic6.ListFillRange = "Criteria!A91:C94"
Sheet1.CmbBox_Dynamic6.ListRows = 4
Sheet1.CmbBox_Dynamic6.LinkedCell = "Criteria!E90"
Sheet1.CmbBox_Dynamic6.Value = 1
End If
End If
End SubThanks in advance for all your assistance.
Cheers!
Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."
