Solved: Evaluating a string and using it as VBA code

[Chris_E]

Hi team,

I want to be able to hide a number of controls depending on other selections in my form. This one gets me started but I don't know how to use the resulting string!!

This works:

    Dim Apart As String
    Dim BPart As String
    Dim StateStr As String

    Apart = "Me.InstallQtyExistingLineI2_"
    BPart = ".Visible=False"

    For i = 1 To 10
    StateStr = Apart & i & BPart
    MsgBox StateStr
    Next i

The strings "Me.InstallQtyExistingLineI2_1.Visible=False" to "Me.InstallQtyExistingLineI2_1o.Visible=False" are messaged up ok.

How do I make these strings useable in VBA to actually hide the 10 controls?

Hope you can help!?

Chris

 

[OBP]

Chris, can you be a bit more explicit with want you want to happen?
I assume that you want something like
if Me.InstallQtyExistingLineI2_1 = "No" then do one thing
if Me.InstallQtyExistingLineI2_1 = "Yes" then do something else.

 

[Chris_E]

No mate, more complex than that one, though a Case statement will do it.

Simplified as a Case Statement (using simpler vars). Assume all DIMS have been completed:

Select Case OPTA   ' OptA is an optionbox containing values 0 to 10
  Case 0
  Me.ctl1.Visible=False
  Me.ctl2.Visible=False
  Me.ctl3.Visible=False
  Me.ctl4.Visible=False
  Me.ctl5.Visible=False
  Me.ctl6.Visible=False
  Me.ctl7.Visible=False
  Me.ctl8.Visible=False
  Me.ctl9.Visible=False
  Me.ctl10.Visible=False

  Case 1
  Me.ctl1.Visible=True
  Me.ctl2.Visible=False
  Me.ctl3.Visible=False
  Me.ctl4.Visible=False
  Me.ctl5.Visible=False
  Me.ctl6.Visible=False
  Me.ctl7.Visible=False
  Me.ctl8.Visible=False
  Me.ctl9.Visible=False
  Me.ctl10.Visible=False

  Case 2
  Me.ctl1.Visible=True
  Me.ctl2.Visible=True
  Me.ctl3.Visible=False
  Me.ctl4.Visible=False
  Me.ctl5.Visible=False
  Me.ctl6.Visible=False
  Me.ctl7.Visible=False
  Me.ctl8.Visible=False
  Me.ctl9.Visible=False
  Me.ctl10.Visible=False

...
...
...

  Case 10
  Me.ctl1.Visible=True
  Me.ctl2.Visible=True
  Me.ctl3.Visible=True
  Me.ctl4.Visible=True
  Me.ctl5.Visible=True
  Me.ctl6.Visible=True
  Me.ctl7.Visible=True
  Me.ctl8.Visible=True
  Me.ctl9.Visible=True
  Me.ctl10.Visible=True

End Select

As you can see, loads of code for a simple task.

I wanted to use a for next loop to create a string that I could then use as a VBA "command" to switch on or off the .Visible condition for each control, based on the value of optA.

This is because this type of control arrangement happens many times in the form in the guise of "If you choose a value of X for this control, we'll only show you X of these associated controls and we'll set the values of the non-visible ones to FALSE"

So, in a nutshell, see if we can use a loop type of structure to recreate a LONG Case statement as above!


Clear as mud?

Chris

 

[OBP]

Very clear so why not just use a loop with your Case code inside the loop then?
Or am I missing something?

 

[Chris_E]

Cos I'm thick!

Point me in the general direction, wind me up and I'll do the rest myself!

Its converting the string to a command I'm lost on.

The loop and case are quite simple.

C

 

[Chris_E]

Example

Dim cmdvar as string

cmdvar = "Me.MyCtl.Visible=True"

cmdvar << how to make this act like a line of code...

Chris

 

[OBP]

But why put it in a string in the first place?
Why not just use normal VBA depending on what they select.
Without seeing the form and how the selection is made I can't visualise what you are trying to achieve and the method that you are choosing.

 

[Chris_E]

Hi OBP

Here's the set up so far:

Dim StateStr As Variant
Dim Apart As String
Dim BPart As String

Apart = "Me.InstallQtyExistingLineI2_"
BPart = ".Visible="
VisState = "False"

Select Case InstallQtyExistingLine_I2

    Case 0
   ' set all controls to .Visible=False
    
    For i = 1 To 10
    StateStr = Apart & i & BPart & VisState
    MsgBox StateStr
' Use the String "StateStr" to make the controls change visibility.
    Next i
    
    Case 1
  ' set only control #1 to .Visible=True
    
    For i = 1 To 10
            If i = 1 Then
            VisState = "True"
            Else
            VisState = "False"
            End If
    StateStr = Apart & i & BPart & VisState
    MsgBox StateStr

' Use the String "StateStr" to make the controls change visibility.

    Next i

   Case 2
  ' set only control #1 to .Visible=True
    
    For i = 1 To 10
            If i = <=2 Then
            VisState = "True"
            Else
            VisState = "False"
            End If
    StateStr = Apart & i & BPart & VisState
    MsgBox StateStr

' Use the String "StateStr" to make the controls change visibility.

    Next i
...
...
...

See how I us the MSGBOX? What I want is to be able to use the value "StateStr" as a line of code in place of the horrid duplicate statements of the Case original idea!

Probably dead easy!?

Chris

 

[Chris_E]

Attached is a clip from the db.

It shows what happens when I choose "0", "1" or "2".... I get a deliberate MSGBOX, but how do I turn that string shown in the MSGBOX into a command? or am I being really realy dumb?

Chris

 

[OBP]

Chris, you don't you use the 0, 1 or 2 that was chosen, not the text.
Are you using an Option Group?

 

[OBP]

Chris I would use an Option Group for this as it actually gives you values from 1 to 10.

 

[Chris_E]

Yeah, but it consumes more form real estate.

C

 

[Chris_E]

Ok. let me boil this down to raw basics and get the background noise out of the way!

If I had 10 controls, called Me.Ctl_01 to Me.Ctl_10, and I wanted to quickly set properties for these such as .Visible, programatically, how could I do it so didn't need to have 10 lines of code? Instead, I would have a loop.

Doesn;t matter if its True or False, thats a separate process, I just want to understand how to manipulate the number part of the control (01 to 10) to accommodate a loop.

How do I do that please chaps?

Chris

 

[Keebellah]

Hi Chirs,
I'd like to take a look too, but I can't get test.zip to open, CRC error etc.
Have another zip file?

Cheers,
Hans

 

[Chris_E]

Thanks for joining the discussion!

I re-zipped the MDB so see how this one goes!

Chris