PDA

View Full Version : Dumb question - onLoad?



bobdole22
08-27-2013, 08:38 AM
I know VB very well, but suck at VBA. I can't believe I have to ask this but I've searched for over an hour and found nothing.

I have some labels, and such that I set to Visible= False. That works fine, through out the program they become visible based on what you click. But whenever I re-load the program they are Visible = True. Is there a way I can make them always Visible = False onLoad?

Thanks in advance!

raj85
08-27-2013, 08:46 AM
If you are using UserForm then on UserForm_Activate event you can write code that me.lable.visible = False such a way when your form will get loaded lable will be invisible.
Same case if you are using excel sheet just need to use code in Worksheet_Activate event

bobdole22
08-27-2013, 09:08 AM
I put this code on my sheet1:


'Onload
Sub Worksheet_Activate()
Label1.Visible = False
ComboBox1.Visible = False
Label2.Visible = False
ComboBox2.Visible = False
Range("B1") = "Month of:"
End Sub


It works, but it only works when I click the "run" button. I need it to work when the form is loaded form the user.

ZVI
08-27-2013, 10:43 AM
If ActiveX control (Label, ComboBox and so on) is on the sheet or on UserForm then select it and do right click.
Then choose Properties and set Visible to False.
Save Workbook and that property will be saved too.

bobdole22
08-27-2013, 11:21 AM
If ActiveX control (Label, ComboBox and so on) is on the sheet or on UserForm then select it and do right click.
Then choose Properties and set Visible to False.
Save Workbook and that property will be saved too.

That works and is exactly what I've been doing. But afterward, I run the program which makes everything visible. Then when I re-open it, they are Visible = True again.

ZVI
08-27-2013, 11:43 AM
Could you please clarify where the controls are - on UserForm or on Sheet?

If controls are on a sheet then put this sub into ThisWorkbook module

Private Sub Workbook_Open()
' Your code
With Sheets("Sheet1")
.Label1.Visible = False
.ComboBox1.Visible = False
.Label2.Visible = False
.ComboBox2.Visible = False
.Range("B1") = "Month of:"
End With
End Sub


If controls are on a UserForm (Alt-F11 menu Insert - UserForm) then put this code into UserForm's module (Right Click - View Code):


Private Sub UserForm_Initialize() ' The same as Form_Load() in VB
' Your code
Label1.Visible = False
ComboBox1.Visible = False
Label2.Visible = False
ComboBox2.Visible = False
Sheets("Sheet1").Range("B1") = "Month of:"
End Sub

bobdole22
08-27-2013, 11:48 AM
Good idea, that is probably it! It's on sheet. I also tried putting it on Module1.

ZVI
08-27-2013, 12:08 PM
that is probably it!
Not probably - it is exactly ;)


I also tried putting it on Module1.
If you prefer using code in Module1 then Sub Auto_Open() is also triggered at open time even at Application.EnableEvents = False


Sub Auto_Open()
' Your code
With Sheets("Sheet1")
.Label1.Visible = False
.ComboBox1.Visible = False
.Label2.Visible = False
.ComboBox2.Visible = False
.Range("B1") = "Month of:"
End With
End Sub

bobdole22
08-27-2013, 01:07 PM
Thanks! Perfect.

ZVI
08-27-2013, 01:19 PM
Glad it helped!
:beerchug: