Calendar staying open

[John]

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

 

[joel]

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

 

[John]

Hi Joel
Thank you very much, it's working fine
Have a Happy New Year.
Regards
John

 

[John]

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]

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.

 

[John]

Hi Joel
I found Refedit and got it in the Reference list."Thanks"
You wrote this>Then 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

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/showthread.php?t=165975

Microsoft Office Help

 

[joel]

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 th
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 Su

 

[John]

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]

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.

 

[John]

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]

the common problem people have the first time they use it (i did) is th
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 unde
program files in the following folder (or equivalent)

C:\Program Files\Microsoft Office\Office11 (or latest version o
office)


Once the DLL is located you should be able to add the control to you
userform

 

[John]

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