Solved: Evaluating a string and using it as VBA code

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Chris_E

Thread Starter
Joined
Feb 16, 2006
Messages
360
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:

Code:
    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

Joined
Mar 8, 2005
Messages
19,895
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

Thread Starter
Joined
Feb 16, 2006
Messages
360
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:

Code:
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

Joined
Mar 8, 2005
Messages
19,895
Very clear so why not just use a loop with your Case code inside the loop then?
Or am I missing something?
 

Chris_E

Thread Starter
Joined
Feb 16, 2006
Messages
360
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

Thread Starter
Joined
Feb 16, 2006
Messages
360
Example

Dim cmdvar as string

cmdvar = "Me.MyCtl.Visible=True"

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

Chris
 

OBP

Joined
Mar 8, 2005
Messages
19,895
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

Thread Starter
Joined
Feb 16, 2006
Messages
360
Hi OBP

Here's the set up so far:

Code:
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

Thread Starter
Joined
Feb 16, 2006
Messages
360
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
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,895
Chris, you don't you use the 0, 1 or 2 that was chosen, not the text.
Are you using an Option Group?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Chris I would use an Option Group for this as it actually gives you values from 1 to 10.
 

Chris_E

Thread Starter
Joined
Feb 16, 2006
Messages
360
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?:confused:

Chris
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
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

Thread Starter
Joined
Feb 16, 2006
Messages
360
Thanks for joining the discussion!

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

Chris
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top