By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,736 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Error 9: Subscript out of range

P: n/a
Hello guru's!

I have the following code that generates an error. The error occures at
the line

CommandBars("OK Revisie").Controls(vLaag1).Controls(vLaag2).vActio n =
Vstate

With the command ?vAction in the Immediate Window I can see that
vAction gets the right value but in the line where the error is
generated it is empty. All the other variables neatly display their
value when pointing at them in the forementioned line. I have been
looking into this but all I can find are articles related to either an
Excel-bug or arrays. I can not find a relation between those two and my
problem. Is it some kind of datatype error?

Function fMenu(vOnOff As Boolean, vLaag1 As Integer, Optional vLaag2 As
Integer, Optional vLaag3 As Integer)

'With this code the menuitems get enabled/visible in the menubar "My
MenuBar"
'fMenu(1, 2, 1)for example will enable the first command in the second
item of the topmenu

Dim vAction
Dim Vstate

If vOnOff = 1 Then 'Should the item be on or off?
Vstate = True
Else
Vstate = False
End If

If vLaag2 = 0 Then 'topmenu gets disabled, anything below that gets
invisible
vAction = "Enabled"
Else
vAction = "Visible"
End If

'final instruction to switch menuitem on or off
CommandBars("My
MenuBar").Controls(vLaag1).Controls(vLaag2).Contro ls(vLaag3).vAction =
Vstate

End Function

Anyone has any inclination why this happens and how to solve this?

TIA,

Henro

Oct 13 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
I suspect that

CommandBars("OK Revisie").Controls(vLaag1).Controls(vLaag2).vActio n =
Vstate

won't work because "vAction" is not a valid property name for the
object you are trying to manipulate. You might have better luck with
the following (untested) variation:
With CommandBars("OK Revisie").Controls(vLaag1).Controls(vLaag2)
Select Case vAction
Case "Enabled"
.Enabled = Vstate
Case "Visible"
.Visible = Vstate
End Select
End With
Henrootje wrote:
Hello guru's!

I have the following code that generates an error. The error occures at
the line

CommandBars("OK Revisie").Controls(vLaag1).Controls(vLaag2).vActio n =
Vstate

With the command ?vAction in the Immediate Window I can see that
vAction gets the right value but in the line where the error is
generated it is empty. All the other variables neatly display their
value when pointing at them in the forementioned line. I have been
looking into this but all I can find are articles related to either an
Excel-bug or arrays. I can not find a relation between those two and my
problem. Is it some kind of datatype error?

Function fMenu(vOnOff As Boolean, vLaag1 As Integer, Optional vLaag2 As
Integer, Optional vLaag3 As Integer)

'With this code the menuitems get enabled/visible in the menubar "My
MenuBar"
'fMenu(1, 2, 1)for example will enable the first command in the second
item of the topmenu

Dim vAction
Dim Vstate

If vOnOff = 1 Then 'Should the item be on or off?
Vstate = True
Else
Vstate = False
End If

If vLaag2 = 0 Then 'topmenu gets disabled, anything below that gets
invisible
vAction = "Enabled"
Else
vAction = "Visible"
End If

'final instruction to switch menuitem on or off
CommandBars("My
MenuBar").Controls(vLaag1).Controls(vLaag2).Contro ls(vLaag3).vAction =
Vstate

End Function

Anyone has any inclination why this happens and how to solve this?

TIA,

Henro
Oct 14 '06 #2

P: n/a
It turned out that the problem was in referencing to vLaag2 and/or
vLaag3 when their parameters are not filled in the functioncall:

CommandBars("MyMenubar").Controls(3).Controls(0).C ontrols(0).Visible =
False
will produce an error as does
CommandBars("MyMenubar").Controls(3).Controls().Co ntrols().Visible =
False
I must have been blind not to see this.

So I solved it the easy way:

Function fMenu(vOnOff As Boolean, vLaag1 As Integer, Optional vLaag2
As Integer, Optional vLaag3 As Integer)

If Not vLaag2 = 0 Then
If Not vLaag3 = 0 Then

CommandBars("MyMenubar").Controls(vLaag1).Controls (vLaag2).Controls(vLaag3).Visible
= vOnOff
Else

CommandBars("MyMenubar").Controls(vLaag1).Controls (vLaag2).Visible =
vOnOff
End If
Else
CommandBars("MyMenubar").Controls(vLaag1).Enabled = vOnOff
GoTo Exit
End If

Exit:
msgbox "Say Hi to all the people that helped me making this
thing work"

I figured it would be easiest to use this code in the 'On Activate'
event of the forms. That works like a charm except! that changes in the
menu are visible once you click on the menu, not straight away.
Any ideas on how to solve this?

TIA Henro
Gord schreef:
I suspect that

CommandBars("OK Revisie").Controls(vLaag1).Controls(vLaag2).vActio n =
Vstate

won't work because "vAction" is not a valid property name for the
object you are trying to manipulate. You might have better luck with
the following (untested) variation:
With CommandBars("OK Revisie").Controls(vLaag1).Controls(vLaag2)
Select Case vAction
Case "Enabled"
.Enabled = Vstate
Case "Visible"
.Visible = Vstate
End Select
End With
Henrootje wrote:
Hello guru's!

I have the following code that generates an error. The error occures at
the line

CommandBars("OK Revisie").Controls(vLaag1).Controls(vLaag2).vActio n =
Vstate

With the command ?vAction in the Immediate Window I can see that
vAction gets the right value but in the line where the error is
generated it is empty. All the other variables neatly display their
value when pointing at them in the forementioned line. I have been
looking into this but all I can find are articles related to either an
Excel-bug or arrays. I can not find a relation between those two and my
problem. Is it some kind of datatype error?

Function fMenu(vOnOff As Boolean, vLaag1 As Integer, Optional vLaag2 As
Integer, Optional vLaag3 As Integer)

'With this code the menuitems get enabled/visible in the menubar "My
MenuBar"
'fMenu(1, 2, 1)for example will enable the first command in the second
item of the topmenu

Dim vAction
Dim Vstate

If vOnOff = 1 Then 'Should the item be on or off?
Vstate = True
Else
Vstate = False
End If

If vLaag2 = 0 Then 'topmenu gets disabled, anything below that gets
invisible
vAction = "Enabled"
Else
vAction = "Visible"
End If

'final instruction to switch menuitem on or off
CommandBars("My
MenuBar").Controls(vLaag1).Controls(vLaag2).Contro ls(vLaag3).vAction =
Vstate

End Function

Anyone has any inclination why this happens and how to solve this?

TIA,

Henro
Oct 16 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.