Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar staying open
Hi Everyone
I'm using a Calendar to set dates on my form (worksheet) I have to select the cell first before opening the calendar. Question: Is it possible to have it open then select the cell. If Yes is it possible that it stays open and install more then one date. Using XL03 and my code below: Private Sub UserForm_Initialize() ' Check if active cell contains a date. If 'yes' show ' same date on calendar. If 'no' show today's date. If IsDate(ActiveCell.Value) Then Calendar1.Value = DateValue(ActiveCell.Value) Else Calendar1.Value = Date End If End Sub Private Sub Calendar1_Click() ' Transfer date selected on calendar to active cell ' and close UserForm. ActiveCell.Value = Calendar1.Value Unload Me End Sub -------------- Regards John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar staying open
I removed the hide an added an inputbox to the code below. Add a control button to the userform that says Exit and put the hide into the button click function. Private Sub CommandButton1_Click() me.hide End Sub Private Sub UserForm_Initialize() ' Check if active cell contains a date. If 'yes' show ' same date on calendar. If 'no' show today's date. If IsDate(ActiveCell.Value) Then Calendar1.Value = DateValue(ActiveCell.Value) Else Calendar1.Value = Date End If End Sub Private Sub Calendar1_Click() ' Transfer date selected on calendar to active cell ' and close UserForm. Set mycell = Application.InputBox( _ prompt:="Select a cell", Type:=8) mycell.Value = Calendar1.Value End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165975 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar staying open
Hi Joel
Thank you very much, it's working fine Have a Happy New Year. Regards John "joel" wrote in message ... I removed the hide an added an inputbox to the code below. Add a control button to the userform that says Exit and put the hide into the button click function. Private Sub CommandButton1_Click() me.hide End Sub Private Sub UserForm_Initialize() ' Check if active cell contains a date. If 'yes' show ' same date on calendar. If 'no' show today's date. If IsDate(ActiveCell.Value) Then Calendar1.Value = DateValue(ActiveCell.Value) Else Calendar1.Value = Date End If End Sub Private Sub Calendar1_Click() ' Transfer date selected on calendar to active cell ' and close UserForm. Set mycell = Application.InputBox( _ prompt:="Select a cell", Type:=8) mycell.Value = Calendar1.Value End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165975 Microsoft Office Help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar staying open
Hi Joel
I have a small problem I can't fix. If I press the Cancel button on the "Inputbox", I get an error 13 Type mismatch. I tried different error handling but no success. Hope you can still help me Regards John "joel" wrote in message ... I removed the hide an added an inputbox to the code below. Add a control button to the userform that says Exit and put the hide into the button click function. Private Sub CommandButton1_Click() me.hide End Sub Private Sub UserForm_Initialize() ' Check if active cell contains a date. If 'yes' show ' same date on calendar. If 'no' show today's date. If IsDate(ActiveCell.Value) Then Calendar1.Value = DateValue(ActiveCell.Value) Else Calendar1.Value = Date End If End Sub Private Sub Calendar1_Click() ' Transfer date selected on calendar to active cell ' and close UserForm. Set mycell = Application.InputBox( _ prompt:="Select a cell", Type:=8) mycell.Value = Calendar1.Value End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165975 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar staying open
I think you may have to put a RefEdit control on a userform. If the control is not on the userform toolbox then right click the toolbox and select additional controls. If RefEdit is not on the list then Do the following VBA menu - References - RefEdit If it is not in the lsit the "browse" for the DLL here Refedit.dll C:\Program Files\Microsoft Office\Office11 (or latest version of office) Sometimes yo have the RefEdit already installed but it is not pointing to the correct location. go back to Reference menu and browse even though it is in the list. Then use the RefEdit control to like a regular userform t get the cell address. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165975 Microsoft Office Help |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar staying open
Hi Joel
I found Refedit and got it in the Reference list."Thanks" You wrote thisThen use the RefEdit control to like a regular userform t get the cell address ( I don't understand) Sorry but I'm new to this and don't know what to do next. Can you help me out Regards John "joel" wrote in message ... I think you may have to put a RefEdit control on a userform. If the control is not on the userform toolbox then right click the toolbox and select additional controls. If RefEdit is not on the list then Do the following VBA menu - References - RefEdit If it is not in the lsit the "browse" for the DLL here Refedit.dll C:\Program Files\Microsoft Office\Office11 (or latest version of office) Sometimes yo have the RefEdit already installed but it is not pointing to the correct location. go back to Reference menu and browse even though it is in the list. Then use the RefEdit control to like a regular userform t get the cell address. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165975 Microsoft Office Help |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar staying open
I put three items into the same suerform 1) Calendar control 2) RefEdit 3) control button RefEdit returns a string o fthe cell selected. so I modified the Calendar Click function to read the Refedit Private Sub Calendar1_Click() Set mycell = Range(Me.RefEdit1.Value) mycell.Value = Calendar1.Value End Sub You probably want to add some extra code like this Private Sub Calendar1_Click() Do if Me.RefEdit1.Value = "" then msgbox("Select Address with RefEdit Control" else Set mycell = Range(Me.RefEdit1.Value) mycell.Value = Calendar1.Value Exit Do end if loop while Me.RefEdit1.Value = "" End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165975 Microsoft Office Help |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar staying open
Hi Joel
Thank you for your patient. RefEdit is on my Toolbox, you say to put it on the Userform,I can't, it won't go. With your code I get a Compile error. Hope you still have patience with me Regards John "joel" wrote in message ... I put three items into the same suerform 1) Calendar control 2) RefEdit 3) control button RefEdit returns a string o fthe cell selected. so I modified the Calendar Click function to read the Refedit Private Sub Calendar1_Click() Set mycell = Range(Me.RefEdit1.Value) mycell.Value = Calendar1.Value End Sub You probably want to add some extra code like this Private Sub Calendar1_Click() Do if Me.RefEdit1.Value = "" then msgbox("Select Address with RefEdit Control" else Set mycell = Range(Me.RefEdit1.Value) mycell.Value = Calendar1.Value Exit Do end if loop while Me.RefEdit1.Value = "" End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165975 Microsoft Office Help |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar staying open
You can only move controls onto a userform when noi macros are running. Make sure the runing macros are stopped. Try putting a different control on the userform to determine if the problem is with the refedit or some other problem. You are getting a compiler error becuase VBA doesn't recognize the Refedit1. Did you know you can change the default names of the userform and the controls to any thing you want. From the VBA menu View - Properties. The select the userform or one of the contols. You can manually change any of the properties and the property will not change again even if the workbook is closed. The properties can also be changed using VBA code. The Name (line 1 of property window) is the property that you would uses to reference the control from VBA. The caption is the title of the userform and is also available on some of the controls. You also may want to put a label (from the toolbox) above the refedit control to tgive the user instructions on how to use the Userform. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165975 Microsoft Office Help |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar staying open
It looks like VBA won't recognize RefEdit, because I have other userforms and
different control but no Inputbox. I can live with the problem, no big deal, but one last try, can we hide the Cancel button. Best wishes for the New Year. John "joel" wrote in message ... You can only move controls onto a userform when noi macros are running. Make sure the runing macros are stopped. Try putting a different control on the userform to determine if the problem is with the refedit or some other problem. You are getting a compiler error becuase VBA doesn't recognize the Refedit1. Did you know you can change the default names of the userform and the controls to any thing you want. From the VBA menu View - Properties. The select the userform or one of the contols. You can manually change any of the properties and the property will not change again even if the workbook is closed. The properties can also be changed using VBA code. The Name (line 1 of property window) is the property that you would uses to reference the control from VBA. The caption is the title of the userform and is also available on some of the controls. You also may want to put a label (from the toolbox) above the refedit control to tgive the user instructions on how to use the Userform. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165975 Microsoft Office Help |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar staying open
the common problem people have the first time they use it (i did) is the Reference is not pointing to the correct DLL/ this is easy to fix. From VBA Menu - Tools - Reference Press the browse button and find the RefEdit.dll file. It is under program files in the following folder (or equivalent) C:\Program Files\Microsoft Office\Office11 (or latest version of office) Once the DLL is located you should be able to add the control to your userform. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165975 Microsoft Office Help |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar staying open
Hi Joel
I thought you would like to know I got it to work. On my Toolbox Control, I right click "Additional Controls" got a Refedit Control, put in on my UserForm and your code and Voilą. It's working. If you don't use it properly it bugs but that's ok Thanks again Regards John wrote in message ... the common problem people have the first time they use it (i did) is the Reference is not pointing to the correct DLL/ this is easy to fix. From VBA Menu - Tools - Reference Press the browse button and find the RefEdit.dll file. It is under program files in the following folder (or equivalent) C:\Program Files\Microsoft Office\Office11 (or latest version of office) Once the DLL is located you should be able to add the control to your userform. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165975 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting not staying on | Setting up and Configuration of Excel | |||
Text staying put | Excel Discussion (Misc queries) | |||
Staying it its own directory. | Excel Worksheet Functions | |||
Headers staying on top! | Excel Discussion (Misc queries) | |||
Workbook Open & Calendar Control | Excel Programming |