| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help
i posted this original message:
I want to use VB to do the following: if cell h10 is text then upon exit of cell h10 clear cell f10. if cell h10 is a number, then do nothing to cell f10. I want this to apply to cell h10 f10 h11 f11 h12 f12 h13 f13 The following response worked great however I am having one porblem with it: Here is the response: Try this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 10 Or Target.Row 13 Then Exit Sub Application.EnableEvents = False If Target.Column = 8 Then If IsNumeric(Target) = False Then If Len(Target) 0 Then Target.Offset(0, -2) = "" End If End If End If Application.EnableEvents = True End Sub Cells in the F column should remain except whn text is entered in the H column. Clearing F10 etc. will have no effect. THIS WORKED GREAT WHEN I TRIED IT ON A NEW SHEET IN A NEW WORKBOOK, BUT WHEN I APPPLIED IT TO MY WORKSHEET IN PROGRESS IT DID NOT WORK. I RECEIVED A MESSAGE STATING AMBIGUOUS TITLE OR NAME (SOMETHING LIKE THAT IF I RECALL) I DO HAVE ANOTHER MACRO WITH THE SAME HEADING AS THIS MACRO "Private Sub Worksheet_Change(ByVal Target As Range)" THEY ARE BOTH ON SHEET 1 HOW CAN I FIX THIS PROBLEM. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
need help
Remove one of the macros. If they both have code, combine them
You can't have two macros in the same module with the same name and for an event macro to work, it must have this name. -- Regards. Tom Ogilvy "Brian" wrote in message ... i posted this original message: I want to use VB to do the following: if cell h10 is text then upon exit of cell h10 clear cell f10. if cell h10 is a number, then do nothing to cell f10. I want this to apply to cell h10 f10 h11 f11 h12 f12 h13 f13 The following response worked great however I am having one porblem with it: Here is the response: Try this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 10 Or Target.Row 13 Then Exit Sub Application.EnableEvents = False If Target.Column = 8 Then If IsNumeric(Target) = False Then If Len(Target) 0 Then Target.Offset(0, -2) = "" End If End If End If Application.EnableEvents = True End Sub Cells in the F column should remain except whn text is entered in the H column. Clearing F10 etc. will have no effect. THIS WORKED GREAT WHEN I TRIED IT ON A NEW SHEET IN A NEW WORKBOOK, BUT WHEN I APPPLIED IT TO MY WORKSHEET IN PROGRESS IT DID NOT WORK. I RECEIVED A MESSAGE STATING AMBIGUOUS TITLE OR NAME (SOMETHING LIKE THAT IF I RECALL) I DO HAVE ANOTHER MACRO WITH THE SAME HEADING AS THIS MACRO "Private Sub Worksheet_Change(ByVal Target As Range)" THEY ARE BOTH ON SHEET 1 HOW CAN I FIX THIS PROBLEM. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
need help
Tom,
If i post the two the same way they appear in vb will you show me what to remove so that they will both work? "Tom Ogilvy" wrote: Remove one of the macros. If they both have code, combine them You can't have two macros in the same module with the same name and for an event macro to work, it must have this name. -- Regards. Tom Ogilvy "Brian" wrote in message ... i posted this original message: I want to use VB to do the following: if cell h10 is text then upon exit of cell h10 clear cell f10. if cell h10 is a number, then do nothing to cell f10. I want this to apply to cell h10 f10 h11 f11 h12 f12 h13 f13 The following response worked great however I am having one porblem with it: Here is the response: Try this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 10 Or Target.Row 13 Then Exit Sub Application.EnableEvents = False If Target.Column = 8 Then If IsNumeric(Target) = False Then If Len(Target) 0 Then Target.Offset(0, -2) = "" End If End If End If Application.EnableEvents = True End Sub Cells in the F column should remain except whn text is entered in the H column. Clearing F10 etc. will have no effect. THIS WORKED GREAT WHEN I TRIED IT ON A NEW SHEET IN A NEW WORKBOOK, BUT WHEN I APPPLIED IT TO MY WORKSHEET IN PROGRESS IT DID NOT WORK. I RECEIVED A MESSAGE STATING AMBIGUOUS TITLE OR NAME (SOMETHING LIKE THAT IF I RECALL) I DO HAVE ANOTHER MACRO WITH THE SAME HEADING AS THIS MACRO "Private Sub Worksheet_Change(ByVal Target As Range)" THEY ARE BOTH ON SHEET 1 HOW CAN I FIX THIS PROBLEM. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
need help
sure. (if I can figure it out <g)
-- Regards, Tom Ogilvy "Brian" wrote in message ... Tom, If i post the two the same way they appear in vb will you show me what to remove so that they will both work? "Tom Ogilvy" wrote: Remove one of the macros. If they both have code, combine them You can't have two macros in the same module with the same name and for an event macro to work, it must have this name. -- Regards. Tom Ogilvy "Brian" wrote in message ... i posted this original message: I want to use VB to do the following: if cell h10 is text then upon exit of cell h10 clear cell f10. if cell h10 is a number, then do nothing to cell f10. I want this to apply to cell h10 f10 h11 f11 h12 f12 h13 f13 The following response worked great however I am having one porblem with it: Here is the response: Try this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 10 Or Target.Row 13 Then Exit Sub Application.EnableEvents = False If Target.Column = 8 Then If IsNumeric(Target) = False Then If Len(Target) 0 Then Target.Offset(0, -2) = "" End If End If End If Application.EnableEvents = True End Sub Cells in the F column should remain except whn text is entered in the H column. Clearing F10 etc. will have no effect. THIS WORKED GREAT WHEN I TRIED IT ON A NEW SHEET IN A NEW WORKBOOK, BUT WHEN I APPPLIED IT TO MY WORKSHEET IN PROGRESS IT DID NOT WORK. I RECEIVED A MESSAGE STATING AMBIGUOUS TITLE OR NAME (SOMETHING LIKE THAT IF I RECALL) I DO HAVE ANOTHER MACRO WITH THE SAME HEADING AS THIS MACRO "Private Sub Worksheet_Change(ByVal Target As Range)" THEY ARE BOTH ON SHEET 1 HOW CAN I FIX THIS PROBLEM. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
need help
here they are as they appear in vb:
Option Explicit Private Sub worksheet_change(ByVal target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If target.Count 1 Then Exit Sub Application.EnableEvents = False On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 If rngDV Is Nothing Then Exit Sub If Intersect(target, rngDV) Is Nothing Then 'do nothing Else newVal = target.Value Application.Undo oldVal = target.Value target.Value = newVal If target.Column = 11 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else target.Value = oldVal _ & ", " & newVal End If End If End If End If Application.EnableEvents = True End Sub Private Sub worksheet_change(ByVal target As Range) If target.Row < 10 Or target.Row 13 Then Exit Sub Application.EnableEvents = False If target.Column = 8 Then If IsNumeric(target) = False Then If Len(target) 0 Then target.Offset(0, -2) = "" End If End If End If Application.EnableEvents = True End Sub "Brian" wrote: Tom, If i post the two the same way they appear in vb will you show me what to remove so that they will both work? "Tom Ogilvy" wrote: Remove one of the macros. If they both have code, combine them You can't have two macros in the same module with the same name and for an event macro to work, it must have this name. -- Regards. Tom Ogilvy "Brian" wrote in message ... i posted this original message: I want to use VB to do the following: if cell h10 is text then upon exit of cell h10 clear cell f10. if cell h10 is a number, then do nothing to cell f10. I want this to apply to cell h10 f10 h11 f11 h12 f12 h13 f13 The following response worked great however I am having one porblem with it: Here is the response: Try this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 10 Or Target.Row 13 Then Exit Sub Application.EnableEvents = False If Target.Column = 8 Then If IsNumeric(Target) = False Then If Len(Target) 0 Then Target.Offset(0, -2) = "" End If End If End If Application.EnableEvents = True End Sub Cells in the F column should remain except whn text is entered in the H column. Clearing F10 etc. will have no effect. THIS WORKED GREAT WHEN I TRIED IT ON A NEW SHEET IN A NEW WORKBOOK, BUT WHEN I APPPLIED IT TO MY WORKSHEET IN PROGRESS IT DID NOT WORK. I RECEIVED A MESSAGE STATING AMBIGUOUS TITLE OR NAME (SOMETHING LIKE THAT IF I RECALL) I DO HAVE ANOTHER MACRO WITH THE SAME HEADING AS THIS MACRO "Private Sub Worksheet_Change(ByVal Target As Range)" THEY ARE BOTH ON SHEET 1 HOW CAN I FIX THIS PROBLEM. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
need help
Option Explicit
Private Sub worksheet_change(ByVal target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If target.Count 1 Then Exit Sub Application.EnableEvents = False On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 If rngDV Is Nothing Then Exit Sub If Intersect(target, rngDV) Is Nothing Then 'do nothing Else newVal = target.Value Application.Undo oldVal = target.Value target.Value = newVal If target.Column = 11 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else target.Value = oldVal _ & ", " & newVal End If End If End If End If If target.Row < 10 Or target.Row 13 Then Exit Sub If target.Column = 8 Then If IsNumeric(target) = False Then If Len(target) 0 Then target.Offset(0, -2).ClearContents End If End If End If Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Brian" wrote in message ... here they are as they appear in vb: Option Explicit Private Sub worksheet_change(ByVal target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If target.Count 1 Then Exit Sub Application.EnableEvents = False On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 If rngDV Is Nothing Then Exit Sub If Intersect(target, rngDV) Is Nothing Then 'do nothing Else newVal = target.Value Application.Undo oldVal = target.Value target.Value = newVal If target.Column = 11 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else target.Value = oldVal _ & ", " & newVal End If End If End If End If Application.EnableEvents = True End Sub Private Sub worksheet_change(ByVal target As Range) If target.Row < 10 Or target.Row 13 Then Exit Sub Application.EnableEvents = False If target.Column = 8 Then If IsNumeric(target) = False Then If Len(target) 0 Then target.Offset(0, -2) = "" End If End If End If Application.EnableEvents = True End Sub "Brian" wrote: Tom, If i post the two the same way they appear in vb will you show me what to remove so that they will both work? "Tom Ogilvy" wrote: Remove one of the macros. If they both have code, combine them You can't have two macros in the same module with the same name and for an event macro to work, it must have this name. -- Regards. Tom Ogilvy "Brian" wrote in message ... i posted this original message: I want to use VB to do the following: if cell h10 is text then upon exit of cell h10 clear cell f10. if cell h10 is a number, then do nothing to cell f10. I want this to apply to cell h10 f10 h11 f11 h12 f12 h13 f13 The following response worked great however I am having one porblem with it: Here is the response: Try this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 10 Or Target.Row 13 Then Exit Sub Application.EnableEvents = False If Target.Column = 8 Then If IsNumeric(Target) = False Then If Len(Target) 0 Then Target.Offset(0, -2) = "" End If End If End If Application.EnableEvents = True End Sub Cells in the F column should remain except whn text is entered in the H column. Clearing F10 etc. will have no effect. THIS WORKED GREAT WHEN I TRIED IT ON A NEW SHEET IN A NEW WORKBOOK, BUT WHEN I APPPLIED IT TO MY WORKSHEET IN PROGRESS IT DID NOT WORK. I RECEIVED A MESSAGE STATING AMBIGUOUS TITLE OR NAME (SOMETHING LIKE THAT IF I RECALL) I DO HAVE ANOTHER MACRO WITH THE SAME HEADING AS THIS MACRO "Private Sub Worksheet_Change(ByVal Target As Range)" THEY ARE BOTH ON SHEET 1 HOW CAN I FIX THIS PROBLEM. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
need help
Tom,
thank you All i did was remove the end sub from the first macro and it worked perfect. Listen, you helped me out with a rather difficult programming issue a couple of years ago and I really appreciated it. I am novice at best when it comes to excel and vb. However in my line of work we don't have many computer guys so they picked the guy who will do least damage, me. I have another issue which is not so bad but bad enough for me. I have a validation box that is designed to be able to picked from a name list located on another sheet. With this I can selected multiple text items from the list and have them inserted into one cell, example would be: I click the arrow in the drop down list and 50 or so text choices are listed and i can individually click as many as i want and they will be placed in lets say cell g20. There is a macro that is used to accomlish this, in fact it was the first macro before the one you just helped me with. However when i unlock the cells containing the drop down list and then protect the sheet the multiple validation function stops working and I can then only place one text item in a cell. the problem is not corrected util i unprotect the sheet and close and reopen the workbook. I actually was actually directed to a web page from this forum that had a macro written for this function and it too has the same problem when the function is perform during the sheet protection. If it is ok with you, maybe I could somehow paste that workbook here or direct you to the site where I got the macro and then you could check it out, this would be the last thing that i need to comlete this 4 month headache. Thank You |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
need help
Posting binaries in the newsgroup is frowned on.
If you want to send me something, my email address is not disguised. -- Regards, Tom Ogilvy "Brian" wrote in message ... Tom, thank you All i did was remove the end sub from the first macro and it worked perfect. Listen, you helped me out with a rather difficult programming issue a couple of years ago and I really appreciated it. I am novice at best when it comes to excel and vb. However in my line of work we don't have many computer guys so they picked the guy who will do least damage, me. I have another issue which is not so bad but bad enough for me. I have a validation box that is designed to be able to picked from a name list located on another sheet. With this I can selected multiple text items from the list and have them inserted into one cell, example would be: I click the arrow in the drop down list and 50 or so text choices are listed and i can individually click as many as i want and they will be placed in lets say cell g20. There is a macro that is used to accomlish this, in fact it was the first macro before the one you just helped me with. However when i unlock the cells containing the drop down list and then protect the sheet the multiple validation function stops working and I can then only place one text item in a cell. the problem is not corrected util i unprotect the sheet and close and reopen the workbook. I actually was actually directed to a web page from this forum that had a macro written for this function and it too has the same problem when the function is perform during the sheet protection. If it is ok with you, maybe I could somehow paste that workbook here or direct you to the site where I got the macro and then you could check it out, this would be the last thing that i need to comlete this 4 month headache. Thank You |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
