Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
ambiguous name detected?
i have a set of code/macro called Change for my sheets, which hides
rows/enters formulas for whenever a cell 'b3' is changed to another option from a validated list. however, for some reason now, a Microsoft Visual Basic notice box pops up and says "Ambiguous name detected: Change" when i imput data into any cell. any ideas as to why? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
ambiguous name detected?
It would help to see the code and know where it was put.
Ambiguous name usually means you have two routines with the same name within the same 'scope'. Also, worksheets can have an event associated with a change in a cell on the sheet, it would be within the worksheet's code module. It would be named Private Sub Worksheet_Change(...) 'code to execute when a change occurs on the worksheet End Sub Look in your VBA project for the word Change and see where it appears, and that will probably clue you in on either 2 routines with the same name, or if VBA is being confused somehow by a routine named Change and the Worksheet_Change() event processor for that worksheet. "Derrick" wrote: i have a set of code/macro called Change for my sheets, which hides rows/enters formulas for whenever a cell 'b3' is changed to another option from a validated list. however, for some reason now, a Microsoft Visual Basic notice box pops up and says "Ambiguous name detected: Change" when i imput data into any cell. any ideas as to why? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
ambiguous name detected?
Check your earlier post.
Derrick wrote: i have a set of code/macro called Change for my sheets, which hides rows/enters formulas for whenever a cell 'b3' is changed to another option from a validated list. however, for some reason now, a Microsoft Visual Basic notice box pops up and says "Ambiguous name detected: Change" when i imput data into any cell. any ideas as to why? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
ambiguous name detected?
Hi J.
This is pretty simple I'm assuming. I've got the ambiguous name detected error coming and I know why, I just don't know how to fix it. I've got Sheet 1, coded with the following 2 (of many) macros (they are in object sheet1 and not in a module - to which I don't know the difference, which is better?) They both have the same name..Private Sub Worksheet_Change (ByVal Target as Range) - but when I try to change either name, (e.g to ..._Change1 ...or ...rowheightchange...) it won't work. How can I change the name so both work? TIA. Macros Below. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 8 Or 9 Or 10 Then Select Case .Value Case "Not Started": ..Interior.ColorIndex = 2 'White ..Font.ColorIndex = 1 Case "Completed": ..Interior.ColorIndex = 5 'Blue ..Font.ColorIndex = 2 Case "Manageable Issues": ..Interior.ColorIndex = 6 'Yellow ..Font.ColorIndex = 1 Case "Significant Issues": ..Interior.ColorIndex = 3 'Red ..Font.ColorIndex = 2 Case "On Track": ..Interior.ColorIndex = 10 ' Green ..Font.ColorIndex = 2 End Select End If End With ws_exit: Application.EnableEvents = True End Sub __ Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText Then Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 Application.ScreenUpdating = True End If End With End Sub -- I''ve encountered an error and I need to close for the weekend :) "JLatham" wrote: It would help to see the code and know where it was put. Ambiguous name usually means you have two routines with the same name within the same 'scope'. Also, worksheets can have an event associated with a change in a cell on the sheet, it would be within the worksheet's code module. It would be named Private Sub Worksheet_Change(...) 'code to execute when a change occurs on the worksheet End Sub Look in your VBA project for the word Change and see where it appears, and that will probably clue you in on either 2 routines with the same name, or if VBA is being confused somehow by a routine named Change and the Worksheet_Change() event processor for that worksheet. "Derrick" wrote: i have a set of code/macro called Change for my sheets, which hides rows/enters formulas for whenever a cell 'b3' is changed to another option from a validated list. however, for some reason now, a Microsoft Visual Basic notice box pops up and says "Ambiguous name detected: Change" when i imput data into any cell. any ideas as to why? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
ambiguous name detected?
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
ambiguous name detected?
You can have more than one event type in a sheet module but only one type of
each event. If it were my workbook/sheet I would dump any merged cells so I could get rid of that event code for autofitting. I hate merged cells with a passion due to the many problems they cause. They are not worth all the hassles of trying to work around them. Gord Dibben MS Excel MVP On Tue, 7 Jul 2009 11:33:02 -0700, Anders wrote: Hi J. This is pretty simple I'm assuming. I've got the ambiguous name detected error coming and I know why, I just don't know how to fix it. I've got Sheet 1, coded with the following 2 (of many) macros (they are in object sheet1 and not in a module - to which I don't know the difference, which is better?) They both have the same name..Private Sub Worksheet_Change (ByVal Target as Range) - but when I try to change either name, (e.g to ..._Change1 ...or ...rowheightchange...) it won't work. How can I change the name so both work? TIA. Macros Below. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 8 Or 9 Or 10 Then Select Case .Value Case "Not Started": .Interior.ColorIndex = 2 'White .Font.ColorIndex = 1 Case "Completed": .Interior.ColorIndex = 5 'Blue .Font.ColorIndex = 2 Case "Manageable Issues": .Interior.ColorIndex = 6 'Yellow .Font.ColorIndex = 1 Case "Significant Issues": .Interior.ColorIndex = 3 'Red .Font.ColorIndex = 2 Case "On Track": .Interior.ColorIndex = 10 ' Green .Font.ColorIndex = 2 End Select End If End With ws_exit: Application.EnableEvents = True End Sub __ Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText Then Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 Application.ScreenUpdating = True End If End With End Sub |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
ambiguous name detected?
To Don - duh - i'm sitting in the corner with a tall pointy hat on. :) I got
it. To Gord - I tried dumping my merged cells, but ran into an issue. My problem is that this sheet is for others to input information on. When I use "center across selection" I run into the following: 1. it's not clear what cell the individual should click on to add text/data - if they click on the wrong one - then the center across selection (CAS) applies to a smaller selection. E.G. If i have columns a-d formatted as CAS, and the individual clicked in C and typed, then the centering only takes place between rows C and D. If I put in a filler spot for [enter text here] - it centers across the selection. If they click that those words, they are entering NOT in the correct cell. See my problem? I'm all for not having merged cells - i get that, but I can't spend all day formatting and copy/pasting for people who didn't find the right cell to input in. I will have to distribute a copy of this sheet to 20+ users for them to fill in and save to a share drive, which I then have to collate into one report (which is where the formatting exercise would take place) Granted, this is a twice a month thing, anyway I can reduce wasted time I'm in for. What's the better solution? I'm game for anything. TIA -- I''ve encountered an error and I need to close for the weekend :) "Gord Dibben" wrote: You can have more than one event type in a sheet module but only one type of each event. If it were my workbook/sheet I would dump any merged cells so I could get rid of that event code for autofitting. I hate merged cells with a passion due to the many problems they cause. They are not worth all the hassles of trying to work around them. Gord Dibben MS Excel MVP On Tue, 7 Jul 2009 11:33:02 -0700, Anders wrote: Hi J. This is pretty simple I'm assuming. I've got the ambiguous name detected error coming and I know why, I just don't know how to fix it. I've got Sheet 1, coded with the following 2 (of many) macros (they are in object sheet1 and not in a module - to which I don't know the difference, which is better?) They both have the same name..Private Sub Worksheet_Change (ByVal Target as Range) - but when I try to change either name, (e.g to ..._Change1 ...or ...rowheightchange...) it won't work. How can I change the name so both work? TIA. Macros Below. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 8 Or 9 Or 10 Then Select Case .Value Case "Not Started": .Interior.ColorIndex = 2 'White .Font.ColorIndex = 1 Case "Completed": .Interior.ColorIndex = 5 'Blue .Font.ColorIndex = 2 Case "Manageable Issues": .Interior.ColorIndex = 6 'Yellow .Font.ColorIndex = 1 Case "Significant Issues": .Interior.ColorIndex = 3 'Red .Font.ColorIndex = 2 Case "On Track": .Interior.ColorIndex = 10 ' Green .Font.ColorIndex = 2 End Select End If End With ws_exit: Application.EnableEvents = True End Sub __ Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText Then Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 Application.ScreenUpdating = True End If End With End Sub |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
ambiguous name detected?
You have a few options.
1. combine the two events into one............difficult. 2. train users to manually fit the row heights in the merged areas. 3. use center across selection and shade the input cell so's users know which cell to enter the text. i.e. shade C1. Select A1:E1 and "center across" Text is entered in C1. Gord On Tue, 7 Jul 2009 12:46:01 -0700, Anders wrote: To Don - duh - i'm sitting in the corner with a tall pointy hat on. :) I got it. To Gord - I tried dumping my merged cells, but ran into an issue. My problem is that this sheet is for others to input information on. When I use "center across selection" I run into the following: 1. it's not clear what cell the individual should click on to add text/data - if they click on the wrong one - then the center across selection (CAS) applies to a smaller selection. E.G. If i have columns a-d formatted as CAS, and the individual clicked in C and typed, then the centering only takes place between rows C and D. If I put in a filler spot for [enter text here] - it centers across the selection. If they click that those words, they are entering NOT in the correct cell. See my problem? I'm all for not having merged cells - i get that, but I can't spend all day formatting and copy/pasting for people who didn't find the right cell to input in. I will have to distribute a copy of this sheet to 20+ users for them to fill in and save to a share drive, which I then have to collate into one report (which is where the formatting exercise would take place) Granted, this is a twice a month thing, anyway I can reduce wasted time I'm in for. What's the better solution? I'm game for anything. TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ambiguous worksheet change | Excel Discussion (Misc queries) | |||
Errors detected while saving ??? | Excel Discussion (Misc queries) | |||
Deliberately ambiguous time format? (No AM/PM) | Excel Discussion (Misc queries) | |||
Ambiguous error | Excel Discussion (Misc queries) | |||
why does ON = Ambiguous in substitution formula? | Excel Worksheet Functions |