Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbutton to Workbook Open
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbutton to Workbook Open
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 "Richard" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbutton to Workbook Open
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 -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=516694 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbutton to Workbook Open
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. "davesexcel" wrote: 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 -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=516694 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbutton to Workbook Open
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbutton to Workbook Open
Yes, that works, I just could not figure out the Globel thing! Thank you so
much. I new there was a way! "GS" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbutton to Workbook Open
I'm glad it works, and pleased to help!
Regards, GS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to: Open closed workbook/Search data tables/Return data to open workbook | Excel Discussion (Misc queries) | |||
CommandButton to open a Excel workbook | Excel Discussion (Misc queries) | |||
CommandButton in Excel to open another Application | Excel Worksheet Functions | |||
Delete a Commandbutton from another Workbook | Excel Programming | |||
Close WorkBook with CommandButton | Excel Programming |