About Us

Die_Another_Day

Drahos wrote:
Dear Charles,
thank you very much for all your help and support. The macto is not
working yet. If you agree I can send you the file to allow you to see it
whole. I suppose there some small error.
Please, let me know.
Drahos

"Die_Another_Day" wrote:

It should be something like:
Set MyCal = Sheets("Sheet1").Shapes("Calendar1")
change Sheet1 to the sheet with your calendar

Charles

Drahos wrote:
Hi Charles,
it is very kind of you to help me. But I am a beginner and I have no idea
what do you mean by your statement "<=== change to correct declaration". What
this correct declaration shoud be?
I am trying to test to open the file and I receive the compile error
showing the statement. I have changed calendar to calender1 but it is not the
right one declaration.
Thanks a lot again.
Drahos.

"Die_Another_Day" wrote:

I think you're going to have to test for the calendar on open. Under
the "ThisWorkbook" object in the editor, try this code:
Private Sub Workbook_Open()
Dim myCal as Object
On Error Resume Next
Set myCal = Calendar '<=== change to correct declaration
On Error Goto 0
If myCal Is Nothing The
Msgbox "personal message"
Sheets("Help").Activate
Exit Sub
End If
End Sub

That's the closest I can think of to get us there.

Charles

Drahos wrote:
Hi Charles,

thank you very much for your help. The macro still does not work as I
wish. I have copied the macro you have mentioned. I understand that the secon
part of the text in Error Handler (after=)
If Err.Number = 'Number you get when it's broke
should be a number which gives me the Excel. But I receive a message
Compile Error
Can't find project or library
and debuger shows
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
as wrong.

Do you have, please, any idea?

Thanks a lot again.
Drahos.

"Die_Another_Day" wrote:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
On Error Goto ErrorHandler
If Not
Application.Intersect(Range("D11,E20,E65,G30,D30,D 33,D54,H71"), _
Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
Exit Sub

ErrorHandler:
If Err.Number = 'Number you get when it's broke Then
MsgBox "If the PopUp Calendar does not work have a look to the
HELP SHEET"
Sheets("Help Sheet").Activate
Else
Err.Raise
End If

On Error Goto 0
End Sub

Charles

Drahos wrote:
Hi Charles,
thank you very much for your reply and help. I am not so good to aply
your advice to my Code. I copy it and I would like to ask you to help me
again to put a new error message to this Code.

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mmmm d, yyyy"
ActiveCell.Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Application.Intersect(Range("D11,E20,E65,G30,D30,D 33,D54,H71"),
Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub

I would like to have the error message like this:
Text = MsgBox("If the PopUp Calendar does not work have a look to the HELP
SHEET")

Best Regards
Drahos

"Die_Another_Day" wrote:

Sub YourSub()
On Error Goto ErrorHandler
'Your code
Exit Sub
ErrorHandler:
if Err.Number = 'Known Erro Number Then
'Your Msg
Else
Err.Raise
End If
On Error Goto 0
End Sub

Charles Chickering

Drahos wrote:
Hi all,
I have a sheet where I use a sheet code with the macro to use Calendar
Object. This sheet is supposed to be used by a lot of other users. If a used
has not the Calendar object on its PC installed than this macro shows an
error and error message. I need to replace this system message by my own
message instructing a user that it is necessary to install/activate the
calendar objet. Is it possible?
Thanks a lot for any help.
Drahos.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Trouble with Code to change sheet name	JOUIOUI	Excel Programming	3	July 6th 06 04:15 PM
Sheet change code	Gordon[_2_]	Excel Programming	2	May 3rd 06 11:08 PM
Overflow error - same code, different sheet	rwnelson	Excel Programming	6	March 30th 06 05:31 PM
How do I change . to : when input in a time sheet by error	geordie	New Users to Excel	2	December 10th 05 08:24 PM
Change Listbox Sheet reorder code	Stuart[_21_]	Excel Programming	3	May 2nd 05 07:26 AM

Menu

About Us