referring to different variable's values in a loop

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Hi all,

I'm trying to refer to a different variable each time I loop through a
procedure, as below:

I'm trying to loop through the different Menu1ItemCount,
Menu2ItemCount etc variables so as to determine how many times to loop
through one part of the code. So basically I want the variable
CurrMenu to be able to equal the variables Menu1ItemCount,
Menu2ItemCount etc. So I've tried to do that by "CurrMenu = "Menu" &
MenuNo & "ItemCount" where MenuNo being the 1, 2, 3 etc so then
CurrMenu will reflect the value of the correct variable - not the text
name of the variable! What sort of variable does CurrMenu need to be
set to (I have it as an object but that doesn't work), or what line do
I need to tell the system that its not text, its actually supposed to
be the value of the variable??


Dim MenuNo, ItemNo As Integer
Dim Menu1ItemCount, Menu2ItemCount, Menu3ItemCount, Menu4ItemCount,
Menu5ItemCount, Menu6ItemCount, Menu7ItemCount As Integer
Dim Menu8ItemCount, Menu9ItemCount As Integer
Dim MenuCount As Integer
Dim CurrMenu As Object

'# of Menus
MenuCount = 5

'# of Items in each Menu
Menu1ItemCount = 3
Menu2ItemCount = 6
Menu3ItemCount = 8
Menu4ItemCount = 2
Menu5ItemCount = 3

ItemNo = 1
MenuNo = 1

Do Until MenuNo = MenuCount + 1

CurrMenu = "Menu" & MenuNo & "ItemCount" 'should equal the variable's
value not the variable's name!

Do Until ItemNo = CurrMenu

Form_MainMenu.Controls("Menu" & MenuNo & "Lbl" &
ItemNo).Visible = False

ItemNo = ItemNo + 1

Loop

MenuNo = MenuNo + 1

Loop
 
I'm not sure I've understood exactly what you want in the end, but it looks
like you want an array:

Dim aiMenu(1 to 5) As Integer
Dim i As Integer
Dim strName As String

aiMenu(1) = 3
aiMenu(2) = 6
aiMenu(3) = 8
aiMenu(4) = 2
aiMenu(5) = 3

For i = 1 to 5
strName = "Menu" & i & "Lbl" & aiMenu(i)
Me(strName).Visible = False
Next


BTW, Bob, you should also be aware that declarations are different in VBA
than in some other languages. The declaration:
Dim MenuNo, ItemNo As Integer
does not give you 2 integers.
MenuNo has no type specified, so you get a variant (default type), and an
integer.
You probably intended:
Dim MenuNo As Integer, ItemNo As Integer
 
I'm not sure I've understood exactly what you want in the end, but it looks
like you want an array:

Dim aiMenu(1 to 5) As Integer
Dim i As Integer
Dim strName As String

aiMenu(1) = 3
aiMenu(2) = 6
aiMenu(3) = 8
aiMenu(4) = 2
aiMenu(5) = 3

For i = 1 to 5
strName = "Menu" & i & "Lbl" & aiMenu(i)
Me(strName).Visible = False
Next

BTW, Bob, you should also be aware that declarations are different in VBA
than in some other languages. The declaration:
Dim MenuNo, ItemNo As Integer
does not give you 2 integers.
MenuNo has no type specified, so you get a variant (default type), and an
integer.
You probably intended:
Dim MenuNo As Integer, ItemNo As Integer

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html

















- Show quoted text -
Click to expand...

Thanks Allen, you were right about the variables, I didn't realise
that was the case.

Also though, I want to have a variable number of elements in each
group... So I've got Menu1, Menu2, Menu3, Menu4, Menu5 for example
(although I might want to add a Menu6 and Menu7 later) and each has 2,
4, 4, 5 and 3 items respectively. How do I loop through each Menu and
then inside that loop, loop through each menu item to make it
invisible, while still being able to add more Menu's and Items later
easily?
 
How will your code know how many items there are in the end?
One you have determined that, you can ReDim the array to that many items,
e.g.:
iHowMany = 9
ReDim aiMenu(1 to iHowMany)

I'm not really clear what you are doing here, but I think you are trying to
use controls on the form to simluate a menu. If so, it might be easier to
put the menu items in a table rather than hard code them.

If you are actually referring to items on command bars, I have not
understood you correctly.
 
How will your code know how many items there are in the end?
One you have determined that, you can ReDim the array to that many items,
e.g.:
iHowMany = 9
ReDim aiMenu(1 to iHowMany)

I'm not really clear what you are doing here, but I think you are trying to
use controls on the form to simluate a menu. If so, it might be easier to
put the menu items in a table rather than hard code them.

If you are actually referring to items on command bars, I have not
understood you correctly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.








- Show quoted text -
Click to expand...

Hi Allen,

Yes I am doing a menu which is labels which pop out from another
label. How would you suggest I add them to a table then bring them
back out and add graphics to them and also execute the correct
commands when clicked?
 
I wouldn't even try to do that, Bob.

It sounds like the kind of thing people do on web pages with JavaScript,
rather than the kind of approach that would be productive in Access where
command bars give this kind of functionality.
 
I wouldn't even try to do that, Bob.

It sounds like the kind of thing people do on web pages with JavaScript,
rather than the kind of approach that would be productive in Access where
command bars give this kind of functionality.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.








- Show quoted text -
Click to expand...

Hi Allen,

I've already done the menus - I just put a bold label with no
background at the top of the page then labels with backgrounds under
them to look kinda like a web menu and just messed about with the
visibility of them in the mousemove action. It works really well and
looks great but I wanted to clean it up a little - thats why I wanted
loops in there. Its a lot of lines for something quite simple but it
took no time at all to do though. I've put the code below. Do you
have any suggestions on how I could clean it up?

Cheers,

Bob.

Const UnselectedBG As Long = 12632256
Const SelectedBG As Long = 11316396

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As
Single, Y As Single)

'Menu1
Menu1Lbl1.Visible = False
Menu1Lbl2.Visible = False
Menu1Lbl3.Visible = False

'Menu2
Menu2Lbl1.Visible = False
Menu2Lbl2.Visible = False
Menu2Lbl3.Visible = False
Menu2Lbl4.Visible = False
Menu2Lbl5.Visible = False
Menu2Lbl6.Visible = False

'Menu3
Menu3lbl1.Visible = False
Menu3lbl2.Visible = False
Menu3lbl3.Visible = False
Menu3lbl4.Visible = False
Menu3lbl5.Visible = False
Menu3lbl6.Visible = False
Menu3lbl7.Visible = False
Menu3lbl8.Visible = False

'Menu4
Menu4lbl1.Visible = False
Menu4lbl2.Visible = False

'Menu5
Menu5lbl1.Visible = False
Menu5lbl2.Visible = False
Menu5lbl3.Visible = False

'Menu6
Menu6Lbl1.Visible = False
Menu6lbl2.Visible = False
Menu6lbl3.Visible = False
Menu6lbl4.Visible = False

End Sub

Private Sub Menu1Lbl1_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu1Lbl1.BackColor = SelectedBG '11316396
Menu1Lbl2.BackColor = UnselectedBG
Menu1Lbl3.BackColor = UnselectedBG

End Sub

Private Sub Menu1Lbl2_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu1Lbl1.BackColor = UnselectedBG
Menu1Lbl2.BackColor = SelectedBG
Menu1Lbl3.BackColor = UnselectedBG

End Sub
Private Sub Menu1Lbl3_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu1Lbl1.BackColor = UnselectedBG
Menu1Lbl2.BackColor = UnselectedBG
Menu1Lbl3.BackColor = SelectedBG

End Sub

Private Sub Menu2Lbl1_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu2Lbl1.BackColor = SelectedBG
Menu2Lbl2.BackColor = UnselectedBG
Menu2Lbl3.BackColor = UnselectedBG
Menu2Lbl4.BackColor = UnselectedBG
Menu2Lbl5.BackColor = UnselectedBG
Menu2Lbl6.BackColor = UnselectedBG

End Sub
Private Sub Menu2Lbl2_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu2Lbl1.BackColor = UnselectedBG
Menu2Lbl2.BackColor = SelectedBG
Menu2Lbl3.BackColor = UnselectedBG
Menu2Lbl4.BackColor = UnselectedBG
Menu2Lbl5.BackColor = UnselectedBG
Menu2Lbl6.BackColor = UnselectedBG

End Sub
Private Sub Menu2Lbl3_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu2Lbl1.BackColor = UnselectedBG
Menu2Lbl2.BackColor = UnselectedBG
Menu2Lbl3.BackColor = SelectedBG
Menu2Lbl4.BackColor = UnselectedBG
Menu2Lbl5.BackColor = UnselectedBG
Menu2Lbl6.BackColor = UnselectedBG

End Sub
Private Sub Menu2Lbl4_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu2Lbl1.BackColor = UnselectedBG
Menu2Lbl2.BackColor = UnselectedBG
Menu2Lbl3.BackColor = UnselectedBG
Menu2Lbl4.BackColor = SelectedBG
Menu2Lbl5.BackColor = UnselectedBG
Menu2Lbl6.BackColor = UnselectedBG

End Sub
Private Sub Menu2Lbl5_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu2Lbl1.BackColor = UnselectedBG
Menu2Lbl2.BackColor = UnselectedBG
Menu2Lbl3.BackColor = UnselectedBG
Menu2Lbl4.BackColor = UnselectedBG
Menu2Lbl5.BackColor = SelectedBG
Menu2Lbl6.BackColor = UnselectedBG

End Sub
Private Sub Menu2Lbl6_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu2Lbl1.BackColor = UnselectedBG
Menu2Lbl2.BackColor = UnselectedBG
Menu2Lbl3.BackColor = UnselectedBG
Menu2Lbl4.BackColor = UnselectedBG
Menu2Lbl5.BackColor = UnselectedBG
Menu2Lbl6.BackColor = SelectedBG

End Sub

Private Sub Menu3Lbl1_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.BackColor = SelectedBG
Menu3lbl2.BackColor = UnselectedBG
Menu3lbl3.BackColor = UnselectedBG
Menu3lbl4.BackColor = UnselectedBG
Menu3lbl5.BackColor = UnselectedBG
Menu3lbl6.BackColor = UnselectedBG
Menu3lbl7.BackColor = UnselectedBG
Menu3lbl8.BackColor = UnselectedBG

End Sub
Private Sub Menu3Lbl2_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.BackColor = UnselectedBG
Menu3lbl2.BackColor = SelectedBG
Menu3lbl3.BackColor = UnselectedBG
Menu3lbl4.BackColor = UnselectedBG
Menu3lbl5.BackColor = UnselectedBG
Menu3lbl6.BackColor = UnselectedBG
Menu3lbl7.BackColor = UnselectedBG
Menu3lbl8.BackColor = UnselectedBG

End Sub
Private Sub Menu3Lbl3_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.BackColor = UnselectedBG
Menu3lbl2.BackColor = UnselectedBG
Menu3lbl3.BackColor = SelectedBG
Menu3lbl4.BackColor = UnselectedBG
Menu3lbl5.BackColor = UnselectedBG
Menu3lbl6.BackColor = UnselectedBG
Menu3lbl7.BackColor = UnselectedBG
Menu3lbl8.BackColor = UnselectedBG

End Sub
Private Sub Menu3Lbl4_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.BackColor = UnselectedBG
Menu3lbl2.BackColor = UnselectedBG
Menu3lbl3.BackColor = UnselectedBG
Menu3lbl4.BackColor = SelectedBG
Menu3lbl5.BackColor = UnselectedBG
Menu3lbl6.BackColor = UnselectedBG
Menu3lbl7.BackColor = UnselectedBG
Menu3lbl8.BackColor = UnselectedBG

End Sub
Private Sub Menu3Lbl5_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.BackColor = UnselectedBG
Menu3lbl2.BackColor = UnselectedBG
Menu3lbl3.BackColor = UnselectedBG
Menu3lbl4.BackColor = UnselectedBG
Menu3lbl5.BackColor = SelectedBG
Menu3lbl6.BackColor = UnselectedBG
Menu3lbl7.BackColor = UnselectedBG
Menu3lbl8.BackColor = UnselectedBG

End Sub
Private Sub Menu3Lbl6_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.BackColor = UnselectedBG
Menu3lbl2.BackColor = UnselectedBG
Menu3lbl3.BackColor = UnselectedBG
Menu3lbl4.BackColor = UnselectedBG
Menu3lbl5.BackColor = UnselectedBG
Menu3lbl6.BackColor = SelectedBG
Menu3lbl7.BackColor = UnselectedBG
Menu3lbl8.BackColor = UnselectedBG

End Sub
Private Sub Menu3Lbl7_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.BackColor = UnselectedBG
Menu3lbl2.BackColor = UnselectedBG
Menu3lbl3.BackColor = UnselectedBG
Menu3lbl4.BackColor = UnselectedBG
Menu3lbl5.BackColor = UnselectedBG
Menu3lbl6.BackColor = UnselectedBG
Menu3lbl7.BackColor = SelectedBG
Menu3lbl8.BackColor = UnselectedBG

End Sub
Private Sub Menu3Lbl8_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.BackColor = UnselectedBG
Menu3lbl2.BackColor = UnselectedBG
Menu3lbl3.BackColor = UnselectedBG
Menu3lbl4.BackColor = UnselectedBG
Menu3lbl5.BackColor = UnselectedBG
Menu3lbl6.BackColor = UnselectedBG
Menu3lbl7.BackColor = UnselectedBG
Menu3lbl8.BackColor = SelectedBG

End Sub

Private Sub Menu4Lbl1_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu4lbl1.BackColor = SelectedBG
Menu4lbl2.BackColor = UnselectedBG

End Sub

Private Sub Menu4Lbl2_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu4lbl1.BackColor = UnselectedBG
Menu4lbl2.BackColor = SelectedBG

End Sub

Private Sub Menu5Lbl1_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu5lbl1.BackColor = SelectedBG
Menu5lbl2.BackColor = UnselectedBG
Menu5lbl3.BackColor = UnselectedBG

End Sub

Private Sub Menu5Lbl2_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu5lbl1.BackColor = UnselectedBG
Menu5lbl2.BackColor = SelectedBG
Menu5lbl3.BackColor = UnselectedBG

End Sub
Private Sub Menu5Lbl3_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu5lbl1.BackColor = UnselectedBG
Menu5lbl2.BackColor = UnselectedBG
Menu5lbl3.BackColor = SelectedBG

End Sub
Private Sub Menu6Lbl1_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu6Lbl1.BackColor = SelectedBG
Menu6lbl2.BackColor = UnselectedBG
Menu6lbl3.BackColor = UnselectedBG
Menu6lbl4.BackColor = UnselectedBG

End Sub

Private Sub Menu6Lbl2_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu6Lbl1.BackColor = UnselectedBG
Menu6lbl2.BackColor = SelectedBG
Menu6lbl3.BackColor = UnselectedBG
Menu6lbl4.BackColor = UnselectedBG

End Sub
Private Sub Menu6Lbl3_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu6Lbl1.BackColor = UnselectedBG
Menu6lbl2.BackColor = UnselectedBG
Menu6lbl3.BackColor = SelectedBG
Menu6lbl4.BackColor = UnselectedBG

End Sub
Private Sub Menu6Lbl4_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu6Lbl1.BackColor = UnselectedBG
Menu6lbl2.BackColor = UnselectedBG
Menu6lbl3.BackColor = UnselectedBG
Menu6lbl4.BackColor = SelectedBG

End Sub






Private Sub lblMenu1_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu1Lbl1.Visible = True
Menu1Lbl2.Visible = True
Menu1Lbl3.Visible = True


End Sub
Private Sub lblMenu2_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu2Lbl1.Visible = True
Menu2Lbl2.Visible = True
Menu2Lbl3.Visible = True
Menu2Lbl4.Visible = True
Menu2Lbl5.Visible = True
Menu2Lbl6.Visible = True

End Sub

Private Sub lblMenu3_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu3lbl1.Visible = True
Menu3lbl2.Visible = True
Menu3lbl3.Visible = True
Menu3lbl4.Visible = True
Menu3lbl5.Visible = True
Menu3lbl6.Visible = True
Menu3lbl7.Visible = True
Menu3lbl8.Visible = True

End Sub

Private Sub lblMenu4_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu4lbl1.Visible = True
Menu4lbl2.Visible = True

End Sub
Private Sub lblMenu5_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu5lbl1.Visible = True
Menu5lbl2.Visible = True
Menu5lbl3.Visible = True

End Sub
Private Sub lblMenu6_MouseMove(Button As Integer, Shift As Integer, X
As Single, Y As Single)

Menu6Lbl1.Visible = True
Menu6lbl2.Visible = True
Menu6lbl3.Visible = True
Menu6lbl4.Visible = True

End Sub
 
Back
Top