Thread: Mike H - Help
View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Tia Tia is offline
external usenet poster
 
Posts: 64
Default Mike H - Help

Hi Joel,

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

Thanks!

"Joel" wrote:

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

Application.Run "Personal.xls!CloseCal"

Sub CloseCal()
frmCalendar.hide
End Sub


"Tia" wrote:

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,

"Mike H" wrote:

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



"Tia" wrote:

That worked!!! Thanks! Now if I save your file, is a calendar going to
always pop up if I click in one of those cells in a new workbook?

"Mike H" wrote:

Hi,

It's probably because of my later version of excel. Open VB editor and
double click the userform i created and delete the calendar from the userform.

With the userform selected click
View|Toolbox and see if you have a calendar control in the toolbox. If you
have click it and drag a calendar to the same size as the userform. You
should now be OK.

If you haven't got a calendar control click TOOLS-Additional controls and
scroll down until you find

Calendar Control x
X could be any number up to (I think) 12
Check the checkbox then OK and you should now have a calendar control in
your toolbox to put on the userform.

Mike





"Tia" wrote:


It works, but for some reason the dates on the calendar aren't showing up.


"Mike H" wrote:

Hi,

File is fixed here

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

I just noticed your using Excel 97 and I only have 2003 so if you can't open
it then post again and I'll post help on how to do it

Mike

"Tia" wrote:

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

"Mike H" wrote:

Hi,

I'm sure that if you upload your workbook to the site below and post the
link then someone will help

http://www.savefile.com/

Mike

"Tia" wrote:

Is anyone willing to take a look at my excel workbook and tell me what I'm
doing wrong? I've followed the steps at www.fontstuff.com but I'm having no
luck getting the calendar to pop-up when I click on a specific cell.

Thanks,

Tia

Was this post helpful to you?