Hopefully this is a very simple one...

I need to get a variable based on a string much the same as the way 'controls' works. The red section in my code is what I need:

Cell = 1
Do Until Cell > 240
If Controls("C" & Cell).BackColor = 255 Then Debug.Print variable("strC" & Cell)
Cell = Cell + 1
Loop

I realise the above is not correct but was the easiest way to show what I mean.

And before you ask, yes I do have 240 variables for this (well constants actually).

I haven't done this before as I haven't had the need; can it be done?

Thanks in advance.

try eval()

I need to get a variable based on a string much the same as the way 'controls' works. The red section in my code is what I need:

Cell = 1
Do Until Cell > 240
If Controls("C" & Cell).BackColor = 255 Then Debug.Print variable("strC" & Cell)
Cell = Cell + 1
LoopWhat are you trying to get with the green section? If you don't have what you need yet, I'd be interested if you could elaborate on this...

eval doesn't want to work (2434 invalid syntax) because the variables (actually constants) called strC1, strC2 etc. are strings with the time i.e:

strC1 = "07:00"
strC2 = "07:15"
etc.

ajetrumpet, I have some textboxes (C1, C2, etc.) which when you click on them the background colour changes. This routine checks if the colour of each cell is 255 (red) and if so it takes an action.

Rather than 240 individual contants this is when you'd want to use an array with 240 elements.

I must admit I am a little worried by a design which has more than 240 controls on a single form. I wonder if this could benefit by a fresh look at the design

This literally SCREAMS OUT for normalization. If you have 240 items on a form, you could probably reduce it considerably by making a child table and matching sub-form in datasheet mode (or make it LOOK like datasheet mode) to display the times. The only trick would be the color change stuff, which looks to me like a "kludge" to tell you something needs changing. Sometimes it doesn't pay to be subtle. Brute force with Access is often better because it will be cleaner in the long run than something tricky that currently depends on a color value. One that a subsequent designer could change behind your back if you weren't careful.

I must agree with all the rest of the posts now and say that the amount of controls you have involved here is a lot of independent ANYTHINGS on a single form. I'm not going to question why it IS this way, but I can say this...I have some textboxes (C1, C2, etc.) which when you click on them the background colour changes. This routine checks if the colour of each cell is 255 (red) and if so it takes an action.

If all the constants are in textboxes in the form, loop through the controls and perform your action based on the condition...dim c as control

for each c in me.controls

if type of c is textbox AND c.backcolor = 255 then
PERFORM ACTION HERE
end if

next c

It's basically a calendar and if it's red it's 'in use' and if it's not then it's 'not in use'. Clicking each textbox toggles this.

I cannot avoid having 240 controls on the form (although there is probably a better way than textboxes because they NEVER contain any text, but at the moment I just want to get it working and will then look at simplifying).

Thanks for that ajetrumpet, but I already have the routine for that... What I need is to get the value of a constant where the name of the constant is based on a text string.

eval() doesn't like it (I assume because it can't 'evaluate' a string like "07:00", "07:15" etc.

I have sorted it by using:

Cell = 1
Do Until Cell > 240
If Controls("C" & Cell).BackColor = 255 Then Debug.Print GetTime(Cell)
Cell = Cell + 1
Loop

and replacing the constants with this function:

Private Function GetTime(Cell As String)
Select Case Cell
Case 1
GetTime = "07:00"
Case 2
GetTime = "07:15"
Case 3
GetTime = "07:30"
...
...
Case 240
GetTime = "18:45"
End Select
End Function

Perhaps not the most elegant solution, but it's much better than what I had before.

Thanks