copy and use vba code

[Giz]

Hi,
I am relatively new to using vba. I have successfully recorded and used
macros, and also have created simple procedures in vba to use as a macro.
What I am trying to do now is copy and use vba code in excel and am having
trouble.

The situation is this: I found vba code in these forums that I would like to
use. What I did was copy the code into a module in the VBAProject
(Personal.XLS), where all my aforementioned macros are located. However, when
I attempt to find the macro via the Tools>Macro>Macros menu item, it is not
listed (all the other macros are). I then tried a new module for the xls
project in which I am working in, again no luck. The only difference I see
between the vba code I am trying to copy and all the other code in my modules
is that the vba code I am trying to copy begins with "Private Sub" rather
than "Sub", and in the parenthesis on the first line is text (ByVal Target As
Range), whereas all of the other procedures are just blank parenthesis ( ).

I have saved the projects after inserting, shut down and rebooted, etc., but
no luck. I thought this would be relatively simple but I can't figure it out,
I am sure I am missing something here that will make me feel silly. Thanks
for any help.

 

[Gary''s Student]

Not all macros are in standard modules. Some may be in worksheet code or
ThisWorkbook code.

 

[Giz]

Thanks for the reply. OK then, here is the code. How do I apply it?

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

 

[Gary''s Student]

At the bottom of the Excel window, right-click the tab name.
Select View Code
Paste you sub in there.

 

[Giz]

The sub is in there. But I still can't get it to work, and I can't see it in
the macros. hmmmmm.

 

[Rookie 1st class]

Change
Private Sub Worksheet_Change(ByVal Target As Range)
to
Public Sub Worksheet_Change(ByVal Target As Range)

 

[JLatham]

First, that code is supposed to be part of a worksheet's code module and you
really aren't supposed to see it. The Private keeps it from showing up in
the list of Macros available to be used by you.

Being part of the Worksheet's code it will respond and do its actions when a
change in a value on that worksheet (if it is in the proper location) takes
place.

To see if it is in the proper location, select the sheet you want it to be
associated with, or that you think it is associate with, and right-click on
the sheet's name tab. Choose [View Code] from the list and you should then
see the code. If you don't, then it is not in the right place - copy and
paste it into the empty module that shows up in VBE when you choose the [View
Code] option from the list.

Now examine the code and realize that it is not going to do anything until a
cell that is made up of merged cells AND has the Wrap Text option turned on
has a change made to it. That's the first test that's made in the module.

Good luck with this.