Changing form object properties (Excel 2000)

Topic

New Reply

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?

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

You never let me have any fun! 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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote