Mike H - Help

[Tia]

Hey Mike,

Is there any way to make the calendar close automatically once a date is
selected? Also, I moved the calendar to another workbook but need to know if
I want a calendar to pop-up on different worksheets within that workbook if I
have to put the code in every tab.

Thanks,

sTia,

Sadly no. To do that you would need to create the userform in your
Personal.xls at the moment it exist only in that particular workbook.

What you do is go back to vb editor right click the userform in project
explorer and remove the userform and you will be asked whether you want to
exppport it fiirst. Click OK

Then import it in personal.xls

Now insert a module in personal.xls along these lines

Sub RunCal()
frmCalendar.Show
End Sub

and finaly change the code that calls the calendar in your workbook to this


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B5, C5, A10, A15, B22, D30")) Is Nothing Then
Application.Run "Personal.xls!RunCal"
End If
End Sub

Mike

Was this post helpful to you?

 

[Joel]

add a second macr to personal.xls to close the form

Application.Run "Personal.xls!CloseCal"

Sub CloseCal()
frmCalendar.hide
End Sub

 

[Tia]

Hi Joel,

I'm a novice at Excel and don't really know how to add another macro.

Thanks!

 

[Tia]

Hi Joel,

I'm a novice at Excel and don't really know how to add another macro.

Thanks!

 

[Joel]

1) type Alt-F11 from worksheet to open VBA window
2) If VBA Project window is not display then from VBA window go to menu View
- Project Explorer
3) Look in Project Explorer window for Personal.xls. You should find the
macro

Sub RunCal()
frmCalendar.Show
End Sub

4) Copy the new macro CloseCal below the RunCal like this

Sub RunCal()
frmCalendar.Show
End Sub

Sub CloseCal()
frmCalendar.hide
End Sub

 

[Tia]

Hey Joel,

I'm not sure if it matters but I'm not using the Personal.xls folder. I'm
using VBAProject (Proposal Form.xls). This is what I have in it already so I
added your code to the bottom, but it doesn't close the calendar once a date
is selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("E6")) Is Nothing Then
frmCalendar.Show
End If
End Sub

Sub CloseCal()
frmCalendar.Hide
End Sub


Thanks,
Tia

 

[Joel]

I don't know what you Proposal Form.xls contains. You have to manually close
the calendar by adding a Control Button (ENTER) to the Proposal Form.xls.
Then when the user selects the date and press Enter the code I supplied will
HIDE the Calendar until it is used again. the button will need a click
function where you would hide the calendar.

 

[Tia]

Are you able to open the file attached below and see where I should add your
code?

http://www.savefile.com/files/1927731

Thanks,

 

[Joel]

Send me your email address and I will send you the updated file.

 

[Tia]

Can you upload it at the same website?

www.savefile.com

It's free, all you have to do is go to the blue boxes on the left side
titled "upload your file".

 

[Joel]

http://www.savefile.com/files/1935712

Can you upload it at the same website?

www.savefile.com

It's free, all you have to do is go to the blue boxes on the left side
titled "upload your file".

 

[Tia]

Thanks for all your help!!!!

http://www.savefile.com/files/1935712