can't figure out this bug

M

majikman

Private Sub viewDS()
With Application.CommandBars.ActionControl
If ActiveWorkbook.Charts("DS SALES$").Visible = xlSheetVisibl
Then
.State = msoButtonUp
'If .State = msoButtonUp Then
ActiveWorkbook.Charts("DS SALES$").Visible
xlSheetVeryHidden
.State = msoButtonUp
ActiveWorkbook.Charts("DS MARGIN$").Visible
xlSheetVeryHidden
.State = msoButtonUp
ActiveWorkbook.Charts("DS Cumm Sales $").Visible
xlSheetVeryHidden
.State = msoButtonUp
ActiveWorkbook.Charts("DS Cumm Marg $").Visible
xlSheetVeryHidden
.State = msoButtonUp
Else
ActiveWorkbook.Charts("DS SALES$").Visible
xlSheetVisible
* .State = msoButtonDown
ActiveWorkbook.Charts("DS MARGIN$").Visible
xlSheetVisible
.State = msoButtonDown
ActiveWorkbook.Charts("DS Cumm Sales $").Visible
xlSheetVisible
.State = msoButtonDown
ActiveWorkbook.Charts("DS Cumm Marg $").Visible
xlSheetVisible
.State = msoButtonDown
End If
End With
End Sub

This snipet of code crashes on the line I have marked with an asterik
It gives me the error Object variable or With block variable not set
I've declared the with at the very top though so I don't understand wh
this is crashing. Can someone help me out
 
P

Paul Robinson

Hi
You are missing a second "End if" at the end. The editor often gives
me a message about With...end With, when not closing an If is the real
problem.
You also have some .State and some State lines (without the dot) -
could that be a problem??

regards
Paul
 
D

Dana DeLouis

Not tested, but would any additional ideas here help?

Private Sub viewDS()
Dim Flag
With ActiveWorkbook
Flag = IIf(.Charts("DS SALES$").Visible = xlSheetVisible,
xlSheetVeryHidden, xlSheetVisible)

.Charts("DS SALES$").Visible = Flag
.Charts("DS MARGIN$").Visible = Flag
.Charts("DS Cumm Sales $").Visible = Flag
.Charts("DS Cumm Marg $").Visible = Flag
.Charts("DS SALES$").Visible = Flag
End With
End Sub
 
M

majikman

Paul, one of the if's is commented out so thats not the problem.

Dana, thanks for the help. I can get my script to do what yours does i
i just remove all the .state lines. However, I need those lines a
those lines will either put a check mark or remove a check mark next t
the option that was selected. Your script doesn't have tha
functionality
 
Top