| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Error Messaga from Sheet Code
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Error Messaga from Sheet Code
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Error Messaga from Sheet Code
I don't know your code but set an object to the calendar
On Error Resume Next Set myCal = Calendar '<=== change to correct declaration On Error Goto 0 If myCal Is Nothing The Msgbox "personal message" Exit Sub End If -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Drahos" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Error Messaga from Sheet Code
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Error Messaga from Sheet Code
Hi Bob,
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 this message: Text = MsgBox("If the PopUp Calendar does not work have a look to the HELP SHEET") Best Regards Drahos "Bob Phillips" wrote: I don't know your code but set an object to the calendar On Error Resume Next Set myCal = Calendar '<=== change to correct declaration On Error Goto 0 If myCal Is Nothing The Msgbox "personal message" Exit Sub End If -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Drahos" wrote in message ... 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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Error Messaga from Sheet Code
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Error Messaga from Sheet Code
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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Error Messaga from Sheet Code
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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Error Messaga from Sheet Code
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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Error Messaga from Sheet Code
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. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Error Messaga from Sheet Code
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. |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Error Messaga from Sheet Code
Dear Bob,
thank you for your help. 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 is some small error. Please, let me know. Drahos "Bob Phillips" wrote: I don't know your code but set an object to the calendar On Error Resume Next Set myCal = Calendar '<=== change to correct declaration On Error Goto 0 If myCal Is Nothing The Msgbox "personal message" Exit Sub End If -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Drahos" wrote in message ... 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. |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Error Messaga from Sheet Code
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trouble with Code to change sheet name | Excel Programming | |||
| Sheet change code | Excel Programming | |||
| Overflow error - same code, different sheet | Excel Programming | |||
| How do I change . to : when input in a time sheet by error | New Users to Excel | |||
| Change Listbox Sheet reorder code | Excel Programming | |||
Linear Mode
