newbie VBA help please

G

Guest

Hello, Thanks in advance for your help.

Before I even ask my question, I am far from being a programer and don't
really even know enough to be dangerous. I am developing a template for a
group of 40 sales reps which will be used to track large accounts. The
template requires the use of merged cells which will need to be able to use
word wrap. I followed previous advice and use the following code from Jim
Rech:
''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
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

This works fine, just need it to be automatic. So I added this to the
worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

This mostly works. When I enter text in a merged cell which will require
wrap then press enter, it does not automatically wrap. But when I go back
and click on the cell, it wraps. What do I need to do so I don't have to go
back and click on the cell?

Thanks,
Steve
 
G

Guest

Don't use selection change. Try the change event...

Private Sub Worksheet_Change(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub
 
S

STEVE BELL

If you want it to happen when you edit a cell,
use an event macro - place it in the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

You can make the code more selective using if statements
target.address="$A$1"
target.row = 1
target.column =1
or use the intersect function

write back if you need more help...
 
B

Bob Phillips

Words of warning.

First, if you are playing with VBA, you DO know enough to be dangerous <vbg>

Second, if you can, avoid merged cells. They cause more problems than they
are worth. You can usually design around it.
 
G

Guest

Thanks everyone for your help. I changed my worksheet event from a selection
change to a change event. Now nothing happens, even when I go back and click
on the cell after entering the text.
Also a combination of a change event and an if statement targeting column 4
didn't work.
Any other ideas?
 
S

STEVE BELL

Steve,

Make sure that the event macro is in the sheet module and not in a regular
module.
Here is another version (replace Macro6 with the name of your macro)
Remember that the change event is fired by entering anything into a cell and
that leaving that cell.
It does not fire if you select a cell.

To target column 4 make sure your if statement
If target.column = 4 then

Private Sub Worksheet_Change(ByVal Target As Range)
Call Macro6
End Sub

If this doesn't work
add a Msgbox line to give you a signal
add a msgbox to the other macro to see if it gets called.

Private Sub Worksheet_Change(ByVal Target As Range)
Msgbox "Event happened"
Call Macro6
End Sub

If none of this helps, than show us your code and tell us where it is
located....
 
M

malik641

Okay, this works.
Try this:

Make sure you keep the

PRIVATE SUB WORKSHEET_CHANGE(BYVAL TARGET AS RANGE)
AUTOFITMERGEDCELLROWHEIGHT
END SU

That Jim and Steve told you to change.

and in your *Sub AutoFitMergedCellRowHeight () * macro right above th
first if statement add:

ACTIVECELL.OFFSET(-1, 0).SELECT[/B]

AND HERE IS WHAT YOU END UP WITH.



SUB AUTOFITMERGEDCELLROWHEIGHT()

DIM CURRENTROWHEIGHT AS SINGLE, MERGEDCELLRGWIDTH AS SINGLE
DIM CURRCELL AS RANGE
DIM ACTIVECELLWIDTH AS SINGLE, POSSNEWROWHEIGHT AS SINGLE

ACTIVECELL.OFFSET(-1, 0).SELEC

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

PRIVATE SUB WORKSHEET_CHANGE(BYVAL TARGET AS RANGE)
AUTOFITMERGEDCELLROWHEIGHT
END SU
---------------------------------------------------

Now after you enter the text and you press enter it will change it fo
you automatically and remain to be the cell that you entered the tex
in.

Hope this is what you were looking for
 
G

Guest

Steve,
Thanks for your help here, I really appreciate it.
I do have the code in the sheet module, and the code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call AutoFitMergedCellRowHeight
End Sub

Nothing happens.

When I added MsgBox "event happened" the macro still doesn't fire, but
the msg box does appear as expected, so there must be an issue with the macro.

The following code from Jim Rech is included in module 1:
''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
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

Is this code not compatible with a change event?

Thanks,
Steve
 
S

STEVE BELL

If the msgbox shows, than the event is firing OK.
Put a msgbox at the begining of the called macro to
see if it is being called.

If it is not being called than check the Call statement in the event macro.
Make sure the macro name is spelled correctly (I do this with copy/paste)
Try removing the word "Call".
Make sure that this macro is in a standard module.
Compile your workbook and check for any issues with any of your code.

Also look at the response from malik641...

hth
 
G

Guest

Ok, I now agree, merged cells are definitely evil. I added
ACTIVECELL.OFFSET(-1, 0).SELECT[/B] right before the first IF statement and
get this
"compile error: Wrong number of arguments or invalid property assignment"
the *.select* in the added code is highlighted.

Thanks everyone for your time, I appreciate it.
 
M

malik641

Yeah you will get that if you have ".Select[/B]" in your code. make sure
it says ".Select" WITHOUT the "[/B]".

If you have that "[/B]" in your CODE, delete it and it SHOULD work
fine...at least it does for me...If it STILL happens...then please post
your code again and I'll check it out.
Ok, I now agree, merged cells are definitely evil. I added
ACTIVECELL.OFFSET(-1, 0).SELECT[/B] right before the first IF statement
and
get this
"compile error: Wrong number of arguments or invalid property
assignment"
the *.select* in the added code is highlighted.

Thanks everyone for your time, I appreciate it.
Click to expand...
 
G

Guest

Thanks malik, that did it. I really appreciate the help!



malik641 said:
Yeah you will get that if you have ".Select[/B]" in your code. make sure
it says ".Select" WITHOUT the "[/B]".

If you have that "[/B]" in your CODE, delete it and it SHOULD work
fine...at least it does for me...If it STILL happens...then please post
your code again and I'll check it out.
Ok, I now agree, merged cells are definitely evil. I added
ACTIVECELL.OFFSET(-1, 0).SELECT[/B] right before the first IF statement
and
get this
"compile error: Wrong number of arguments or invalid property
assignment"
the *.select* in the added code is highlighted.

Thanks everyone for your time, I appreciate it.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Merged Cells Autofit Macro Code 1
AutoFit Macro to Reduce row height? 0
Auto fit wrapped and merged cells in entire worksheet Options 0
Row height 3
Merged cell auto row height revisited 3
Autofit of Merged cell when hit enter key 2
Worksheet_Activate not working on multiple cells 1
Auto Re-Sizing Merged Cells 1

Top