What's in a name?

[Brian F]

What is it in the Sub name "Private Sub Set_Merged_Row_Height (ByVal Target As Range)" that makes it so the Visual Basis editor will not recognize the statement? I click "Debug" and nothing happens. (This routine is suppose to set the row height for merged cells).

Private Sub Set_Merged_Row_Height(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim r As Range, c As Range, cc As Range
Dim ma As Range

Set r = Range("AS18:CE52")
If Not Intersect(Target, r) Is Nothing 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 Sub

 

[Joe4]

"Debug" doesn't run VBA code. It is used to locate errors in your code.
How is the macro being called/run?

And why are you using "Target"? This is usually used with Event Procedures, and your code is definitely not an Event Procedure.

Did you try to modify an Event Procedure code?

 

[Brian F]

Debug should go through he code line by line. If I put the code in a module and click "debug" nothing happens. The editor won't even enter the module to start the debug. If I remove "(ByVal Target As Range)" the editor begins to work but the procedure quickly fails.

I got this code off the net and I wanted to go through it line by line to see what it did. Nothing.

I'm too inexperience to know why the author is using "Target". I'm amazed how many procedures off the net don't work. I've only been learning vba for about 3 weeks and I'm making progress but can't avoid asking some pretty naive questions. Thanks for any help you can give me.

The code below actually seems to be doing the job but I need to limit it to the range "AS18:CE52". Could you tell me how to limit the range of this procedure to "AS15:CE52)?


Sub AutoFitMergedCellRowHeight()

Dim CurrentRowHeight As Single
MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single
PossNewRowHeight As Single

If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + _
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

 

[Joe4]

Can you provide a link to where you got the code?
You may be doing some things to it that are not allowed.

"Target" is typically found in Event Procedures. Event Procedures are VBA that happen automatically upon some even happening (i.e. the opening of a file, changing the value in a specific cell, changing worksheets, etc). Event Procedures have very strict naming conditions. Each event has a specific name, and the code MUST be named that - you cannot change it or it will not work. Event Procedures are also placed in Worksheet or Workbook modules only.

On the other hand, VBA code that is not an Event Procedure is typically placed in a Standard module, and has flexibility in how you want to name it. However, these VBA procedures don't use "Target".

So, to best help you, please provide the following:
- Where did you get the code from?
- Exactly what are you trying to do?
- What do you want to trigger this code to run?

 

[Brian F]

I broke a bunch of the rules you outlined for me.

Got the code from here;

http://help.lockergnome.com/office/text-wraps-cell-row-height-auto-set--ftopict764669.html

I am using it in a routine that goes through a many step process of preparing an inspection document to give to a client. It is in a standard module, activated by a form button.

'Macro removes the equations, removes the empty lines, saves the workbook, condenses the field report for the superintendent, prints a PDF version (gives you the opportunity to save the PDF), then further condenses it to "Requires Attention" only items to be copied to another document. All formatting and condensing.

There are lines on the report entered into cells that have been merged. I want the cells to adjust to the height of the text (often 35 separate cell that I don't want to have to manually adjust to the size of the test). But there are cells out of range of the print function that run over the size of the cell and I DON'T want to adjust for them so I need to specify the range to adjust.

 

[Nalani]

Debug should go through he code line by line.



The below actually seems to be doing the job ......

Sub AutoFitMergedCellRowHeight()

Dim CurrentRowHeight As Single
MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single
PossNewRowHeight As Single

To go through your code, you press F8 while in the module.

Your code that you say actually works, well it doesn't for me because 2 of the lines aren't Dimensioned. As you have it, it throws an error : Statement invalid outside Type Block.

It should be like so:

Dim CurrentRowHeight As Single
Dim MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single
Dim PossNewRowHeight As Single

From there, I tested on A1 & B1 being Merged and Wrap Text prior to running the code. It works.

BUT, I agree with Joe4, it would help if you explanied in words what you have and what you need.

 

[Brian F]

Nalani

I have attempted to write a more complete description of what I'm doing.

I was saying that pressing F8 did nothing. The editor wouldn't even highlight the first line of the procedure, which I've take to mean there is an error in the first line.

 

[Brian F]

Nalani,

The corrections you gave me makes the process work on one merged group of cells (which is a good thing). I can write a loop that works its way down the column I want to adjust.

Thank you for your help!

 

[Nalani]

Nalani


I was saying that pressing F8 did nothing. The editor wouldn't even highlight the first line of the procedure, which I've take to mean there is an error in the first line.

That was explained by Joe4 in Post #4. "Target" is used for Event Procedures. You will not be able to Step through an Event Procedure unless there is some type of Change to the Worksheet and you have a Break Point assigned.

I only tested on your second code that you posted which was not an Event Procedure only to point out that there were a couple of mistakes in it.