dattagal
05-16-2007, 01:25 PM
I am stuck.
I am trying to cause certain actions to occur in response to activating a worksheet (clicking on that worksheet's tab - right?) and in response to questions answered on another sheet, but what I am attempting to do it isn't working (and I am sure what I am attempting is the wrong way to go about it anyway) so can someone help?
Just get me started and I will do the rest.
here is the scenario...
when I click on the FormEnglish worksheet tab, I want Excel to look at the value of certain check boxes and hide or unhide rows based on that which ones are checked
for example, I have the Standard and High-Low dental plans and then I have six possible tiers of coverage for each plan
if the user selects the Standard plan (optDentalStandardPlan=true) and the employee only coverage tier (AND optDentalEEOnly = true) I want row 46 to be visible when the user clicks on the FormEnglish worksheet tab.
The Employee and Spouse coverage tier could be selected as well (as one of the enrollee's choices) so if the Standard plan is selected and both employee only and employee and spouse tiers are selected, then I want rows 46 and 49 to be visible while 47, 48, 50 and 51 are hidden (assuming that the coverage levels corresponding with them were NOT selected)
Now I am thinking a loop or a case would best serve this purpose (neither of which I know how to write) but could do so if someone could point me in the right direction (so to speak)
But I can't even get an if-then to work - here is what I tried and no response at all:
'if plan type = Standard AND Employee Only coverage is selected,
'make row 46 visible, otherwise hide it
If Sheets("Questions").obDentalStandardPlan = True And Sheets("Questions").chkDentaEE.Value = True Then
Sheets("FormEnglish").Rows("46:46").Hidden = False
Else: Sheets("FormEnglish").Rows("46:46").Hidden = True
End If
'if plan type = Standard AND Employee & Spouse is selected,
'make row 58 visible otherwise hide it
If Sheets("Questions").obDentalStandardPlan = True And Me.chkDentaEEandSps = True Then
Sheets("FormEnglish").Rows("49:49").Hidden = False
Else: Sheets("FormEnglish").Rows("49:49").Hidden = True
End If
I am really frustrated - sometimes I think I know just enough to be dangerous but then again, we all got to start somewhere
Anyway, any assistance would be greatly appreciated.
I am trying to cause certain actions to occur in response to activating a worksheet (clicking on that worksheet's tab - right?) and in response to questions answered on another sheet, but what I am attempting to do it isn't working (and I am sure what I am attempting is the wrong way to go about it anyway) so can someone help?
Just get me started and I will do the rest.
here is the scenario...
when I click on the FormEnglish worksheet tab, I want Excel to look at the value of certain check boxes and hide or unhide rows based on that which ones are checked
for example, I have the Standard and High-Low dental plans and then I have six possible tiers of coverage for each plan
if the user selects the Standard plan (optDentalStandardPlan=true) and the employee only coverage tier (AND optDentalEEOnly = true) I want row 46 to be visible when the user clicks on the FormEnglish worksheet tab.
The Employee and Spouse coverage tier could be selected as well (as one of the enrollee's choices) so if the Standard plan is selected and both employee only and employee and spouse tiers are selected, then I want rows 46 and 49 to be visible while 47, 48, 50 and 51 are hidden (assuming that the coverage levels corresponding with them were NOT selected)
Now I am thinking a loop or a case would best serve this purpose (neither of which I know how to write) but could do so if someone could point me in the right direction (so to speak)
But I can't even get an if-then to work - here is what I tried and no response at all:
'if plan type = Standard AND Employee Only coverage is selected,
'make row 46 visible, otherwise hide it
If Sheets("Questions").obDentalStandardPlan = True And Sheets("Questions").chkDentaEE.Value = True Then
Sheets("FormEnglish").Rows("46:46").Hidden = False
Else: Sheets("FormEnglish").Rows("46:46").Hidden = True
End If
'if plan type = Standard AND Employee & Spouse is selected,
'make row 58 visible otherwise hide it
If Sheets("Questions").obDentalStandardPlan = True And Me.chkDentaEEandSps = True Then
Sheets("FormEnglish").Rows("49:49").Hidden = False
Else: Sheets("FormEnglish").Rows("49:49").Hidden = True
End If
I am really frustrated - sometimes I think I know just enough to be dangerous but then again, we all got to start somewhere
Anyway, any assistance would be greatly appreciated.