Please Please Please Help Me
Posted by Jason Meredith on January 04, 2000 3:12 PM
Hi there,
I have to tell you that my brain is completely frazzled trying to do a VBA project in Excel for college. I have done a little VB before so I have attempted to code VBA for Excel with the help of the macro recorder. The thing is I've got the hard part done, and I've set up a form that does what I want it to. I have however run up against the three problems below.
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.
Posted by Ivan Moala on January 04, 2000 9:20 PM
Jasy;
1) Try using the Private Sub Workbook_SheetActivate(ByVal Sh As Object)
eg:Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Your sheet name you want to run the routine" Then
Application.Run "Your Routine"
End If
End Sub
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
Posted by Jasy M. on January 05, 2000 8:07 AM
Problem still, how you perform a conditional test to find out the name of the active worksheet?
Hi there,
It's Jasy again. Thank you very much Ivan for your help. I almost got the thing working. It's just on the first point that I am still a little stuck. I tried to incorporate the code I was given last time but it refused to work (Maybe I was doing something wrong).
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.
Posted by Ivan Moala on January 05, 2000 1:50 PM
Re: Problem still, how you perform a conditional test to find out the name of the active worksheet?
Jasy
Try something like;
If Activesheet.Name <> "Your sheet name" then Exit sub
.....the rest of your routine
.....
.....
End sub
Ivan
