Commandbutton to Workbook Open

[Guest]

How would you send code from the CommandButton2 located on a Userform to code
located on Workbook open(). Example:
If Commandbutton2 is "clicked" then GoTo Workbook Open() Inputagain:
I've tried GoTo Inputagain from the CommandButton2 but that doesn't work,
Variable not Defined! Any suggestions. Thanks in Advance

 

[Guest]

Richard,
Can you not put your "Inputagain" code in a general module,
which can be called from Workbook_Open and hence also from your CommandButton?

HTH

 

[davesexcel]

Did you just ask this exact question again,
Copy the macro that is in the workbook open event and place it in your
command button code, you may have to do some adjusting, but that's a
given with xl.
The workbook open event works when you open the workbook

 

[Guest]

Yes I did ask the same question, but I thought I might have misworded it so I
sent it again. Again I can't copy the code for the Workbook Open over to the
CommandButton because I need the code in the workbook open. I need it to Exit
the workbook Open code IF the commandbutton2 is selected because if all the
code is ran and the user selects the commandButton2 with out any input then
this code won't be ran untill next year at that date. Therefore not have any
days added to there vacation. I can remove the CommandButton2 and not give
the user that opition to "Remind me later" but I thought excel could do most
anything. Thanks anyway for your help.

 

[Guest]

Hi Richard,

You need to entirely restructure what you're doing for it to work how you
want it to! If you're interested in how, read on:

First:
Move all the code to a standard module. Enter something like the following
in that module:


Option Explicit

'Declare the following global variable so it can be accessed from anywhere
in your code
'The button on UserForm1 will set its value to TRUE if clicked
Public bRemindMeLater As Boolean


'Put your code in its own procedure, something like this:
Sub CheckVacationDays()

Dim L As Long, M As Long
Dim Ans as Variant

L = Month(Date) * 10000 + Year(Date)
M = GetSetting("Demo", "Drafts", "Month", 0)

If L = M Then Exit Sub

If Month(Date) = 4 Then
If Day(Date) >= 24 And Day(Date) < 27 Then
bReminMeLater = False '**Optional: It's default value is FASLE, but
this line ensures it!!
UserForm1.Show
End If

'Here is the solution to your problem!
'Read the value placed in the variable by the button on UserForm1
If bRemindMeLater Then GoTo InputLater

Ans = MsgBox("Do you qualify for Bank Day?", vbYesNo)
If Ans = vbYes Then
MsgBox ("1 Day has been added to your Bank Days")
SaveSetting "Demo", "Drafts", "Month", L '**Notice no parenthesis
used here!!
Worksheets("Vacation").Range("C3").Value = 1
Else
Worksheets("Vacation").Range("C3").Value = 0 '**Do you really need
this??
End If

InputLater:
End If

End Sub


Second:
Enter the following in ThisWorkbook: '**Replaces what you have now!!

Option Explicit

Private Sub Workbook_Open()
CheckVacationDays
End Sub


Third:
With UserForm1, implement some good practices for naming controls so they're
easier to associate with.
For example, you could rename CommandButton2 to: cmdRemindMeLater
Enter the following in the code behind the userform:

Private Sub cmdRemindMeLater_Click()
bRemindMeLater = True '**Tell us the user clicked the button
Unload Me '**If you want to dismiss the userform here!!
End Sub


I think this should work how you want.
Good luck!
GS

 

[Guest]

Yes, that works, I just could not figure out the Globel thing! Thank you so
much. I new there was a way!

 

[Guest]

I'm glad it works, and pleased to help!

Regards,
GS