1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Evaluating a string and using it as VBA code

Discussion in 'Business Applications' started by Chris_E, Mar 31, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Chris_E

    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
     
  2. OBP

    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.
     
  3. Chris_E

    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
     
  4. OBP

    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?
     
  5. Chris_E

    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
     
  6. Chris_E

    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
     
  7. OBP

    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.
     
  8. Chris_E

    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
     
  9. Chris_E

    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
     

    Attached Files:

  10. OBP

    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?
     
  11. OBP

    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.
     
  12. Chris_E

    Chris_E Thread Starter

    Joined:
    Feb 16, 2006
    Messages:
    360
    Yeah, but it consumes more form real estate.

    C
     
  13. Chris_E

    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
     
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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
     
  15. Chris_E

    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
     
  16. Sponsor

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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/698893

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice