copy and use vba code

[Guest]

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.

 

[Guest]

Sorry - previous post I meant "dialog", not "fialog".!

 

[Guest]

By definition, Privatae Sub procedures won't show in the fialog, as you
discovered. They are usually run by being called by another procedure. In
addition, you can't run a procedure with a parameter, such as ByVal Target as
Range, for example, because you can't supply the required information by
simply running it. It SOUNDS like you've copied an event procedure into a
module and that simply won't fly. That's a procedure that runs, for example,
when an event occurs, like selecting a cell, or changing a value, or opening
a workbook, etc. There are special places to store these procedures and
they're not RUN by the user, they are invoked by the event occurring.
Which is the name of the sub?

 

[Guest]

And here I thought I had learned something new! I had already told several
of my co-workers who were having system problems that it probably had to do
with their fialog being out of sync.



Keith

 

[Guest]

Thanks for the reply. Here is the code below, as I copied it from another
post in this forum. What it does is auto fit row size for cells that are
merged.

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

Any help on how I can get this to work would be appreciated.

 

[Guest]

Giz, maybe you accidentally put the procedure where it belonged. To check it
out, right click on the sheet tabs at the bottom of the window and then click
View Code for each one. If you did put it in the sheet code module, this is
the way to find it.

 

[Guest]

After checking it out, the code is there. I just don't know how to get it to
run. It is not visible in the the Tools>nacro>macros dialog, and it doesn't
seem to just run by itself. thanks for the replies

 

[Gord Dibben]

Giz

This is event code and runs when changes are made on the sheet not when you
click on a button or run it from Tools>Macro>Macros.

The code does not belong in Personal.xls.

It must be placed into the sheet module of the the worksheet in which you have
the merged cells.

Open your workbook then right-click on your sheet tab and "View Code"

Copy/paste the code into that module.

Only works on merged cells in a column, not across rows.

The merged cells must be set for wrap text and rows set for autofit.


Gord Dibben MS Excel MVP

 

[Guest]

Thanks Gord, now I understand, it works now. If I could ask another
question... because the code has to be placed in the sheet module in which
the merged cells are located, does this mean that I have to copy the code
every time to other worksheets/workbooks in which I want to use it, or is
there a location where all my xls projects can access the code?

 

[Gord Dibben]

Giz

Sorry for stating "The code does not belong in Personal.xls."

I misled you with that.

You don't need the code in every sheet.

Edit the first line of the code

Private Sub Worksheet_Change(ByVal Target As Range) to read

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Then place the code into your Personal.xls under ThisWorkbook module found by
expanding Microsoft Excel Objects when in the VBE

The code will be available for any active sheet in any open workbook.


Gord