PDA

View Full Version : code will not work when I change the name



AIDSNGO
04-26-2012, 11:24 AM
I have two bits of code:

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
Dim ProtectStatus As Boolean

With target
If .MergeCells And .WrapText Then
ProtectStatus = Me.ProtectContents
If ProtectStatus Then Me.Unprotect ' "password"
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
On Error Resume Next
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
On Error GoTo 0
Application.ScreenUpdating = True
If ProtectStatus Then Me.Protect ' "password"
End If
End With
End Sub

and

Private Sub worksheet_change(ByVal target As Excel.Range)
Select Case Worksheets("Instructions and Worksheet").Range("E44").Value
Case "1"
Worksheets("Activity #2").Visible = True
Case "2"
Worksheets("Activity #2").Visible = False
End Select
End Sub

Both work by themselves, and need to apply to the same sheet, but when I put both in, it tells me there is an ambiguous name. However, as soon as I change either of the names from "worksheet_change" that code stops working. Help?

Thanks!

Bob Phillips
04-26-2012, 03:41 PM
Which cells does the first code apply to, and which for the second?

Paul_Hossler
04-26-2012, 04:54 PM
If you put both subs into the same worksheet module, and both are called Worksheet_Change, that wold cause problems, including the error message


Private Sub worksheet_change(ByVal target As Excel.Range)


If that IS what you're trying to do, then I think you need to integrate the logic into a single sub

Paul

Teeroy
04-26-2012, 07:54 PM
Alternatively you could take both subs out of the worksheet code container and move them into a separate code module (as sub1 and sub 2 for example) and call them at the worksheet_change event.

e.g.


Private Sub worksheet_change(ByVal target As Range)
Call Sub1
Call Sub2
End Sub