98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Userform Code
Userform Code
Resolved · Medium Priority · Version 2007
Tue 14 May 2013, 13:16Delegate Shaun said...
Shaun has attended:
Excel VBA Intro Intermediate course
Userform Code
I have a userform with the following objects: Frame1, Label1, and Frame2.
Frame1 has several tickboxes.
Label1 contains a warning that all tickboxes must be ticked.
Frame2 contains option buttons to proceed. Frame2 set to visible false in properties.
I want Frame2 to be visible and Label1 to be invisible when all tickboxes ticked and vice versa.
This is the code that I've put into the Userform:
Private Sub UserForm_Click()
If chkLocation.Value = True And chkOfsted.Value = True _
And chkStructure.Value = True And chkLAgree.Value = True _
And chkFIS.Value = True And chkSupport.Value = True _
Then fraFundingType.Visible = True And lblWarning.Visible = False
Else
fraFundingType.Visible = False And lblWarning.Visible = True
End If
End Sub
It's not working, any hints?
Shaun
Wed 15 May 2013, 22:43Trainer Gary said...
RE: Userform Code
Hi Shaun
Thanks for getting in touch. In what way is it not working? Are both showing / neither showing?
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 15 May 2013, 22:55Delegate Shaun said...
RE: Userform Code
Presents with Frame1 and Label1 as it should. When all tickboxes filled in nothing happens. I'm expecting Label1 to disappear and Frame2 to appear.
Shaun
Thu 16 May 2013, 20:41Trainer Gary said...
RE: Userform Code
Hi Shaun
Thanks for clarifying. I think your code logic itself is fine, you need to adjust the event that triggers the test.
Currently you are looking for a userform_change event. You should put these tests under a checkbox_change event. You will probably have to replicate this for all the checkboxes (e.g. chkLocation_change, chkOfsted_change...) so that each are monitored independently.
I hope this helps.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 17 May 2013, 09:19Delegate Shaun said...
RE: Userform Code
Thanks, understood. I've put the same code in the checkbox_change event for each one.
BUT
Now breaks with message "Compile error: else without if"
Shaun
Fri 17 May 2013, 23:44Trainer Gary said...
RE: Userform Code
Hi Shaun
The IF statement is getting a little confused. You should put the line
fraFundingType.Visible = True And lblWarning.Visible = False
on it's own line. It will then understand that the ELSE statement belongs to the rest of the IF block.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 20 May 2013, 08:28Delegate Shaun said...
RE: Userform Code
OK, done that and the break has disappeared.
You're waiting for the BUT!
Here it is.
BUT it's gone back to not reacting as previous.
One thing - I have it in the click event, should it be cgange event?
Shaun
Mon 20 May 2013, 08:39Delegate Shaun said...
RE: Userform Code
Actually tried switching to change and didn't make a difference.
For clarity the code for each tickbox now looks like this:
Private Sub chkFIS_Change()
If chkLocation.Value = True And chkOfsted.Value = True _
And chkStructure.Value = True And chkLAgree.Value = True _
And chkFIS.Value = True And chkSupport.Value = True _
Then
fraFundingType.Visible = True And lblWarning.Visible = False
Else
fraFundingType.Visible = False And lblWarning.Visible = True
End If
End Sub
Shaun
Mon 20 May 2013, 17:55
Edited on Mon 20 May 2013, 17:59Trainer Gary said...
RE: Userform Code
Hi Shaun
I noticed what the problem was: the line where you turn the controls on or off reads:
fraFundingType.Visible = False And lblWarning.Visible = True
By using AND in between the statements this means "are both these controls equal to True?" Every time it was resolving to False, which results in no action. Instead each process should be listed on separate lines.
I've attached a file with this fixed. I've also suggested a rejig: add a macro that does all the showing and hiding, and call that on a checkbox state change. This way round, if you want to change the code you only have to modify it in one place.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
Tue 21 May 2013, 18:49Delegate Shaun said...
RE: Userform Code
Thanks, works OK now.
The comment about the AND operator is a good reminder about following the logic behind it all.
Thanks for the tidying up and tip about calling on routine. I was wondering whether I could do that instead of repeating the whole thing for each tickbox.
Shaun
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:##### displaying in ExcelWhen you get a series of hash symbols (####) appearing in some of your cells in a spreadsheet, this can make you think that you've make some kind of mistake. |
