Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
1. How do you do a conditional test to see if the user has selected a certain worksheet in a workbook? I have code I want to run if one sheet is selected but not if another sheet is selected.
2. How do you load up a form from a sub procedure? I want to code a form to open when a keyboard shortcut is pressed, so I set a sub procedure that calls the form from a module but this doesn't work (I just don't know the syntax).
3. How do get a form to close when its job is done or the Cancel button is pressed without going back into the VB editor (i.e. after it closes to remain in Excel)? Do I need to compile the form in VBA? I have tried End and Unload Me but these take me back into the Visual Basic Editor again when I want to stay in Excel.
I know that I should really get a book and read up but I haven't had the time. These are probably basic questions but, as I have said, it is the first time that I have tried VBA and I don't quite know the Excel syntax. If anyone could help I would greatly appreciate it. Thanks,
Jasy M.
| Check out our Excel VBA Resources | ||||
 |
 |
 |
 |
 |
2) To load up a form from a sub procedure do something like this;
Sub loader()
Load UserForm2
UserForm2.Show
End sub
Where Userform2 is the name of your Userform.
Then in Excel Press "Alt F8" to get the the Macro
Dialog box. From here select your macro = loader
then Click on Options and select your short cut key.
If however you want to load a form via other key
combinations then you will have to look @ online
help for "OnKey"
3) If you run your routine from the VBA editor and the routine finishes then it will take you back into the VBA editor.
To stay in excel run your routine from excel.
To get more help look at the online help in both
Excel & its VBA editor by Pressing F1.
Ivan
1. The code that I have to execute is error checking code to occur when when the Ok button of the form is clicked. I have got all the calculations under this event procedure, but if these calculations took place on one of my worksheets, they would seriously mess things up. Therefore I need a way of saying:-
If the ActiveWorksheet is equal to "Sheet Name" Then End the operation.
I was thinking of something like 'If ActiveSheet.Name = "Sheet Name" Then
"Execute Code"'.
I know this isn't the proper syntax but I was thinking of something like it.
This error check would obviously go in at the start of the OK_Click routine so that no calculations would take place if the wrong sheet was the active one.
Yet again, thanks for your help all those who have given it. I would appreciate just this one bit more as if I can do this then the thing is complete (at last). Thanks,
Jasy M.
If Activesheet.Name <> "Your sheet name" then Exit sub
.....the rest of your routine
.....
.....
End sub
Ivan