I have a spreadsheet that has worked fine for me sincei made it a few months ago. IT calls up a series of user forms and asks for info then places it all where it needs to be.
A co-worker aksed me if I could make it work for her as well and I figured why not it should be as simple as reproducing the code and changing a few pieces of text so it asks the user for the correct information.
Trouble is when I added the relevant pieces of code and the imported the same UserForms no matter what command button I hit it always continues following hte macro script and ignores what the comand button tells it to do.
Here is the code for the first user form.
as you can see it is simple and strait forward
And here is the code that calls the UserForm and tells it what to do.
As you might be able to tell my co-worker only needs this to calculate the shrinkage of one group where as I was using it with 6 groups and had a seperate userform and similar code for each group. And it always worked perfectly allowing me to skip a group cancel the wizard or continue as normal based on the button I would hit. But now if I hit Cancel or CommandButton2 which = Stopped it shows I have hit CommandButton2 unloads the user form and then acts as if Stopped still = False continueing on with the code rather then going to the StopNow line I indicated it should. Am I missing something simple here? Cause I have tried anything and everything I can think of with no luck all the while the workbook I originally wrote this in everything works just fine...
A co-worker aksed me if I could make it work for her as well and I figured why not it should be as simple as reproducing the code and changing a few pieces of text so it asks the user for the correct information.
Trouble is when I added the relevant pieces of code and the imported the same UserForms no matter what command button I hit it always continues following hte macro script and ignores what the comand button tells it to do.
Here is the code for the first user form.
Code:
Option Explicit
Public Run As Boolean
Public Stopped As Boolean
Private Sub CommandButton1_Click()
Run = True
Unload Me
End Sub
Private Sub CommandButton2_Click()
Stopped = True
Unload Me
End Sub
Private Sub UserForm_Initialize()
Me.Label1.Caption = "Would you like to run the Shrinkage Tool Wizard?"
End Subas you can see it is simple and strait forward
And here is the code that calls the UserForm and tells it what to do.
Code:
Public Week As String
Public Run As Boolean
Public RunSSCOE As Boolean
Public Stopped As Boolean
Public STOPSSCOE As Boolean
Public SSCOE As Boolean
Public ALLDONE As Boolean
Option Explicit
Sub Grab_Info()
Week = Range("A1").Value
Stopped = False
UserFormShrink.Show
If Stopped Then GoTo StopNow
Application.DisplayAlerts = False
STOPSSCOE = False
UserFormSSCOE.Show
If STOPSSCOE Then GoTo StopNow
If SSCOE Then GoTo NoMore
If RunSSCOE Then GoTo Group1
Group1: Sheets("temp").Visible = True 'Unhide temp sheet add data, copy average shrinkage and rehide temp"
Sheets("temp").Activate
Cells.Select
ActiveSheet.Paste
Range("A300:AC303").Select
Selection.Copy
Sheets("site averages").Visible = True
Sheets("site averages").Activate
Range("b1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("temp").Visible = False
Sheets("site averages").Visible = False
NoMore: MsgBox "No line groups remain. Wizard complete!"
StopNow: Sheets("Attrition").Activate
Range("A1").Select
Application.DisplayAlerts = True
Exit Sub
End SubAs you might be able to tell my co-worker only needs this to calculate the shrinkage of one group where as I was using it with 6 groups and had a seperate userform and similar code for each group. And it always worked perfectly allowing me to skip a group cancel the wizard or continue as normal based on the button I would hit. But now if I hit Cancel or CommandButton2 which = Stopped it shows I have hit CommandButton2 unloads the user form and then acts as if Stopped still = False continueing on with the code rather then going to the StopNow line I indicated it should. Am I missing something simple here? Cause I have tried anything and everything I can think of with no luck all the while the workbook I originally wrote this in everything works just fine...
