Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    San Jose, California, USA
    Posts
    82
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Changing form object properties (Excel 2000)

    I would like to change the property of a control object based on other input on a form. In my case it should be =IF(x=y,activesheet.checkbox1.visible="true",) to turn on a check box only if other criteria are met. All I get is a #NAME? error.

    Any thoughts?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing form object properties (Excel 2000)

    To change properties of controls, you must use Visual Basic, not a worksheet formula such as =IF(...).

    Is this about a UserForm (something you create and edit in the Visual Basic Editor), or a form as part of a worksheet?

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    San Jose, California, USA
    Posts
    82
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Changing form object properties (Excel 2000)

    Hans

    Thanks. This is a form as part of a worksheet. Objects on the form are placed from the "tools" toolbar. Not being a VB wiz, I might have to look at a different way to get my conditional input.

    Keith

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing form object properties (Excel 2000)

    No need to be a wiz..you can use something like this to activate the checkbox based on cell values.

    <pre>Option Explicit
    Private Sub UserForm_Activate()
    If ThisWorkbook.ActiveSheet.Range("a1").Value = "y" Then
    UserForm1.CheckBox1.Enabled = True
    Else: UserForm1.CheckBox1.Enabled = False
    End If
    End Sub
    </pre>


  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing form object properties (Excel 2000)

    If your "form" is part of a worksheet, you might need code in worksheet events to do what you want. This is far from trivial, even if you're familiar with VB.

    I recommend that you investigate the possibilities of doing without controls.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing form object properties (Excel 2000)

    But Keith wrote that he doesn't use a UserForm, he has a form as part of a worksheet...

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing form object properties (Excel 2000)

    You never let me have any fun! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> I was just pointing out following your suggestion that he use a userform instead of what he is using how he could manipulate the userform controls since he said he wasn't great at using VBA.

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing form object properties (Excel 2000)

    My mistake Hans, I thought you suggested he use a userform, but I see you suggested doing it without controls.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing form object properties (Excel 2000)

    OK Michael, I agree with you that a UserForm is probably easier.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •