Editing cell through formula bar doesn't trigger Worksheet_Change 1

[tbarthen]

I have a range of cells that are merged named "descr": ($B$2:$K$2).

I had a problem (which I fixed) in which you can't pasted certain data to the merged range of cells. The following error messages would pop up when trying to paste:
"Data on clipboard is not the same size and shape as the selected area. Do you want to paste the data anyway?"
<I click OK>
&quot;Can't change part of a merged cell.&quot;
<I click OK>
&quot;Microsoft Excel cannot past the data.&quot;

So my solution was:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range(&quot;descr&quot.Address Then
SendKeys (&quot;{F2}&quot
SendKeys (&quot;^+{HOME}&quot
Application.OnKey &quot;~&quot;, &quot;AutoFitMergedCellRowHeight&quot;
End If
End Sub

This is nice, because it select all the data already present in the range, and I can just paste right over it.

Here's my problem now:

After I hit enter to leave the cell, I expect it to trigger the Worksheet_Change event. It doesn't happen though. Apparently, when you enter data directly in the formula bar (which is basically what I'm doing when I SenKeys &quot;F2&quot, it doesn't trigger this event. It only triggers the event if you enter data when you click on the cell and type or paste it to the cell.

Any ideas how to get this to get it to trigger an event so I can run my sub? FYI: My sub is just to resize the merged range to fit all the data I've pasted.

 

[tbarthen]

FYI:
Ignore this part of my last posting:

Application.OnKey &quot;~&quot;, &quot;AutoFitMergedCellRowHeight&quot;

Its garbage. I was in a rush when I typed that up, and forgot to remove that part.

My hurriedness explains the couple of spelling errors also -oops!

 

[Zathras]

I can't quite follow what you are doing, but if you structure your code so that all that happens on the Worksheet_Change event is to call a sub in a separate code module, then you can simply call that same sub from this routine after you execute the SendKeys.

 

[tbarthen]

Okay, I figured out my problem, and arrived at a new one.:-(
^{|-IGO AHEAD AND SCROLL DOWN IF YOU CARE TO READ THE RESOLUTION TO MY PREVIOUS ISSUE|-I}

HERE'S MY NEW PROBLEM:
I can't seem to pass a range to one of my modules: &quot;AutoFitMergedCellRowHeight,&quot;
I've tried the following lines to call it, but none of them works:
AutoFitMergedCellRowHeight(myrange)
AutoFitMergedCellRowHeight(myrange.Address)
AutoFitMergedCellRowHeight(&quot;myrange&quot;)
The errors for each are:
Run-time error '424' Object required
Compile error Type mismatch
Compile error Type mismatch

The sub I'm calling looks something like this:
Sub AutoFitMergedCellRowHeight(mrgd_cell As Range)
Dim blahblah
With mrgd_cell.MergeArea
'my code
End With
End Sub

I also tried:
Sub AutoFitMergedCellRowHeight(ByVal mrgd_cell As Range)
...and I tried changing the sub to a function.

Regardless of all my attempts, I still get an error when I try to pass a range to this module. Can anyone tell me what I'm doing wrong? >:-<

|-IRESOLUTION TO MY PREVIOUS ISSUE|-I
I was wrong. The Worksheet_Change event actually was being triggered when I copied data to the merged cell range.
THE PROBLEM:
Even though I entered data in the merged cell range &quot;B2:K2&quot; (which I named &quot;descr&quot, it was the first cell in this range, &quot;B2&quot;, that triggered Worksheet_Change.
So the 'If' statement below didn't pass:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myrange As Range
Set myrange = Application.Range(&quot;descr&quot
If Target.Address = Range(&quot;descr&quot;).Address Then
AutoFitMergedCellRowHeight(myrange)
End If
blablahblah
End Sub

My Fix for the If statement is:
If Target.Address = Range(&quot;B2&quot;).Address Then
_{I find it odd that the range &quot;descr&quot; was considered the target when I clicked on it and triggered the Worksheet_SelectionChange event, yet when I entered data in the range, it didn't trigger the Worksheet_Change event - but rather, the first cell, B2 of the &quot;descr&quot; range was the trigger. Anyways, its working now.}

 

[xlbo]

tbarthen - bit of advice - some colour or formatting makes things clearer but too much is ust confusing. You do however, win the award for most colour and formatting styles used in a single posting - well done


Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Zathras]

...The errors for each are:
Run-time error '424' Object required
Compile error Type mismatch
Compile error Type mismatch...

...mrgd_cell As Range...

Since you defined the parameter as a range (which is an object), you need to define [blue]

 myrange

[/color] as a range and all should be well:
[blue]

   Dim myrange As Range

[/color]
Note that both
[blue]

 "myrange"

[/color]
and
[blue]

 myrange.Address

[/color]
are strings, hence the &quot;Type Mismatch&quot; when trying to use it where a range is expected.

 

[tbarthen]

Haha..thanks Geoff. I try to keep my explanation to a minimum, but it got a little long, so I tried to separate things with some formatting. I'll gladly accept that award though

Zathras, I had a feeling you might ignore my |-Iboring explanation|-I at the bottom of that last post, but if you had read it, you'd see that I actually did define myrange.

Here it is again:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myrange As Range
Set myrange = Application.Range(&quot;descr&quot
If Target.Address = Range(&quot;descr&quot.Address Then
AutoFitMergedCellRowHeight(myrange)

It still doesn't work though. CRAZY

I even added the following line to my code to verify that it was a range type, and it was:
MsgBox TypeName(myrange)

If you're losing as much patience over this as I am, I understand

 

[Zathras]

You're right. I didn't read past the point where you began talking about the previous problem.

You've hit on one of Excel's &quot;gotchas&quot; -- The error message is extremely misleading. Sorry, I didn't notice it from the first post.

Change the line of code to either[blue]

   Call AutoFitMergedCellRowHeight(myrange)

[/color]or[blue]

   AutoFitMergedCellRowHeight myrange

[/color]
and it should work for you.

 

[tbarthen]

Thanks Z, you're always a big help in these forums:
I went with: Call AutoFitMergedCellRowHeight(myrange)

As its probably starting to show, I'm a little new at passing ranges. I have one more problem following that fix, and then I hope this monster will be working fine.
HERE IT IS:
After I send my range to AutoFitMergedCellRowHeight, I get

^{Run-time error 1004 application-defined or object-defined error}

The part below in red is what's getting highlighted when I debug:
Sub AutoFitMergedCellRowHeight(mrgd_cell As Range)
mrgd_cell.Select
With mrgd_cell.MergeArea
'more code
End With
'more code
End Sub

First thing I tried was adding that second line:
mrgd_cell.Select
but that didn't help.

Any ideas on this one? Stupid question...of course you do I

 

[Zathras]

Sorry, I can't reproduce the error. Here is what I tried but it didn't generate an error:

Sub test6()
  AutoFitMergedCellRowHeight Range("b1")
End Sub

Sub AutoFitMergedCellRowHeight(mrgd_cell As Range)
        mrgd_cell.Select
        With mrgd_cell.MergeArea
              'more code

[blue]

              MsgBox .Address

[/color]

         End With
         'more code
End Sub

 

[tbarthen]

Comparing your code to mine helped me figure this one out.
In your example, you passed a single cell as a range: AutoFitMergedCellRowHeight Range(&quot;b1&quot;)

I was passing a range of multiple cells for my entire merged range.

I looked up MergeArea in Microsoft VB help, and found the following info:

MergeArea Property
Returns a Range object that represents the merged range containing the specified cell.
Remarks The MergeArea property only works on a single-cell range

FYI: If anyone is interested in the code to autosize the row height of merged cells, check out this site:

http://www.excel-center.com/mailing/137101h.htm

Thanks again for all the help Z