Hi, I've been programming for a few months with C++ but I'm brand new to programming in Visual Basic for Excel.
I'm trying to write code that will automatically wrap and resize a merged cell (spanning many columns, one row) according to how much text is typed into it, and I want it to continually do this automatically every time the cell is edited.
I found that other people have already dealt with this and a guy named Jim Rech had already posted some code somewhere to accomplish this. I tried his code and it worked, but it only worked when I called the macro using Alt+F8. The point of me doing this is that I'm trying to set up a form in excel for people who aren't necessarily computer savvy/attentant enough to reformat things on their own, so having to call the macro using a keyboard shortcut isn't going to cut it. Then I found that other people had asked this and were told to use Worksheet_Active and Worksheet_Change to get the update automatically functionality (by the way, I don't understand at all why it would work this way--could someone explain this, too?). I applied that to the code from Jim Rech, and it updated my range automatically, but instead of autofitting it did something really funky--it de-merged the cells and crammed all the text into the first cell.
I figured I'd try to use the concepts I'd learned and try to write a simpler, hackier version for my purposes, but it's not working and I can't figure out why (go figure). The idea I had in mind was to just take the text from the big merged cell, put it in a "ghost cell" that isn't being used, set the width of the ghost cell to be the same as the width of the original merged cell, then wrap the text in the ghost cell and autofit it, then apply the ghost cell height back to the original merged cell. Seems simple, but apparently not.
Here is my code:
Private Sub Worksheet_Activate()
Call Worksheet_Change(Range("A59:K59"), Range("L59"))
End Sub
Private Sub Worksheet_Change(ByVal r As Range, ghost As Range)
Set ghost.Width = r.Width
Set ghost.Value = r.Value
ghost.WrapText = True
ghost.AutoFit
Set r.RowHeight = ghost.RowHeight
End Sub
VBA is giving me the error message: "Procedure declaration does not match description of event or procedure having the same name." It highlights the line I bolded in my code.
Any help you can give with the error message or my code would be greatly appreciated.
I'm trying to write code that will automatically wrap and resize a merged cell (spanning many columns, one row) according to how much text is typed into it, and I want it to continually do this automatically every time the cell is edited.
I found that other people have already dealt with this and a guy named Jim Rech had already posted some code somewhere to accomplish this. I tried his code and it worked, but it only worked when I called the macro using Alt+F8. The point of me doing this is that I'm trying to set up a form in excel for people who aren't necessarily computer savvy/attentant enough to reformat things on their own, so having to call the macro using a keyboard shortcut isn't going to cut it. Then I found that other people had asked this and were told to use Worksheet_Active and Worksheet_Change to get the update automatically functionality (by the way, I don't understand at all why it would work this way--could someone explain this, too?). I applied that to the code from Jim Rech, and it updated my range automatically, but instead of autofitting it did something really funky--it de-merged the cells and crammed all the text into the first cell.
I figured I'd try to use the concepts I'd learned and try to write a simpler, hackier version for my purposes, but it's not working and I can't figure out why (go figure). The idea I had in mind was to just take the text from the big merged cell, put it in a "ghost cell" that isn't being used, set the width of the ghost cell to be the same as the width of the original merged cell, then wrap the text in the ghost cell and autofit it, then apply the ghost cell height back to the original merged cell. Seems simple, but apparently not.
Here is my code:
Private Sub Worksheet_Activate()
Call Worksheet_Change(Range("A59:K59"), Range("L59"))
End Sub
Private Sub Worksheet_Change(ByVal r As Range, ghost As Range)
Set ghost.Width = r.Width
Set ghost.Value = r.Value
ghost.WrapText = True
ghost.AutoFit
Set r.RowHeight = ghost.RowHeight
End Sub
VBA is giving me the error message: "Procedure declaration does not match description of event or procedure having the same name." It highlights the line I bolded in my code.
Any help you can give with the error message or my code would be greatly appreciated.