ambiguous name detected?

[Derrick]

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?

 

[JLatham]

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.

 

[Dave Peterson]

Check your earlier post.

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?

 

[Anders]

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

 

[Don Guillett]

You DON'T. You incorporate both into ONE.

 

[Gord Dibben]

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

 

[Anders]

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

 

[Gord Dibben]

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