Solved: Previous Cell

[cleturno]

I am using Excel 2003. Let me start off by saying that I do not know the excel libraries to save my life. I got into a project that is over my head and I am now paying for it trying to learn at lightspeed and working through my issues. I should have stuck with .net AHHHHHHHHHHH. :banghead:

What I am trying to accomplish is that I want to auto resize the row height of merged cells. I already have that function written and it works great. The only problem that I am having is that I want the procedure to run when the user presses enter on the current cell, but instead it is going to the next possible cell. For example if I was typing in A1, text wrap/merged cell once I am finished entering my data I press enter. The module is checking cell A2. I need it to check A1, then go back to the next cell. With the module I am using I have to select the cell, but I could change that. The cells may not neccisarily be directly up or down, they could be all over the place.

[Zack Barresse]

Hi there, welcome to VBAX!

I'm not sure what you're trying to do with the cell once you trap the event, but you need a worksheet event here.

Right click on the sheet tab, select View code. You'll see the VBE, with the two dropdowns on top of the right pane, select the left one (it will say General), choose Worksheet. From the right box (it will say SelectionChange), choose Change.

You can delete the SelectionChange event now.

Now in your Worksheet_Change event, the range object Target is referring to A1, or the cell you just entered on. Note this will work for a large selection as well. I would check if the cell was merged as well in that code. Something like this ...


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.MergeCells = True Then
MsgBox "Merged"
Else
MsgBox "Unmerged"
End If
End Sub


HTH

[cleturno]

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
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 is the code under the sheet

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

[Zack Barresse]

The activecell is not using the sheet change events "Target" object. Pass it as a variable to the routine ...

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

Sub AutoFitMergedCellRowHeight(Cell As Range)
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If Cell.MergeCells Then
With Cell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
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

Note this is untested by myself as of yet.

[cleturno]

Ok, that is working fine, but it is not working if there is a formatting change and I get an error when I try to delete something. It shouldn't error on delete becuase it checks the cell first to ensure that it is a merged cell and then it checks to ensure that it is of multiple lines???? Thoughts?

Thanks for that last post. I am just not familar with how excel handles variables and the libraries.

[Zack Barresse]

No problem.

Excel will not notice a formatting change. It doesn't consider that an event and the change event will not catch it. Unfortunately, there is nothing we can do about an action on formatting change.

That is unless you have a cell that returns the format (of whatever you desire) as a value in another cell. Then you can look at this cell for the format. The downside we still deal with is that this new cell will only show the format on a calculation change - which again we're back to trapping a format change, which does not trigger a calculation event. So I think that is a no-go. Sorry.

As far as the delete not working. I'm not sure. Maybe you could put a check for the Target value in your change routine ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "" Then Exit Sub
Call AutoFitMergedCellRowHeight(Target)
End Sub

[cleturno]

Thanks for your help I have got it working. I just used On Error Resume next and that took care of the problem. Thanks for the help