VBA code not auto running - protected sheet

[Athom]

Hello, Im relatively new to VBA codes. I usually manage to work these things out myself, but cant seem to crack it this time...!

Im using a VBA code to autosize row height for merged cells. The code (below) works when I run it, but otherwise doesnt seem to update.

Private Sub Worksheet_Activate()
Public Sub AutoFitAll()
  Call AutoFitMergedCells(Range("a20:d20"))
   Call AutoFitMergedCells(Range("a22:d22"))
    Call AutoFitMergedCells(Range("a24:d24"))
End Sub
Public Sub AutoFitMergedCells(oRange As Range)
  Dim tHeight As Integer
  Dim iPtr As Integer
  Dim oldWidth As Single
  Dim oldZZWidth As Single
  Dim newWidth As Single
  Dim newHeight As Single
  With Sheets("Objective Setting (Dec)")
    oldWidth = 0
    For iPtr = 1 To oRange.Columns.Count
      oldWidth = oldWidth + .Cells(1, oRange.Column + iPtr - 1).ColumnWidth
    Next iPtr
    oldWidth = .Cells(1, oRange.Column).ColumnWidth + .Cells(1, oRange.Column + 1).ColumnWidth
    oRange.MergeCells = False
    newWidth = Len(.Cells(oRange.Row, oRange.Column).Value)
    oldZZWidth = .Range("ZZ1").ColumnWidth
    .Range("ZZ1") = Left(.Cells(oRange.Row, oRange.Column).Value, newWidth)
    .Range("ZZ1").WrapText = True
    .Columns("ZZ").ColumnWidth = oldWidth
    .Rows("1").EntireRow.Autofit
    newHeight = .Rows("1").RowHeight / oRange.Rows.Count
    .Rows(CStr(oRange.Row) & ":" & CStr(oRange.Row + oRange.Rows.Count - 1)).RowHeight = newHeight
    oRange.MergeCells = True
    oRange.WrapText = True
    .Range("ZZ1").ClearContents
    .Range("ZZ1").ColumnWidth = oldZZWidth
  End With
End Sub

Are there any hints or tips out there to get it running?

Thanks!!

 

[6StringJazzer]

To start with, this code will not compile. You start with a Sub, then the very next line is another Sub declaration before you have ended the first Sub. So this can't be what is actually in your file.

Second I am confused by this statement: "The code (below) works when I run it, but otherwise doesnt seem to update." I would not expect the code to update anything if you don't run it. Can you elaborate on what you mean here?

 

[Athom]

Sorry, I'm obviously showing how little I know here!

I added in the sub at the start because I did a little googling (clearly a dangerous thing) and found a thread on another forum where someone had offered that as a fix to the code not auto updating. Interestingly, the VBA does still work when I manually hit run even though the code does not, as you say, make sense.

What I mean by the code not updating, I was under the impression that when you add a VBA code that this would run in the background of your document and make the required changes (maybe when you save the document, or change pages etc), but what is happening is that when I'm in the actual VBA code, I click run and I can see that the document is being updated, but unless i go into the code and hit run its not continuing to run and update in the background. Is that clearer? Maybe i am misunderstanding the way they work, in which case that would be good to know as well.

Basically, I have created a workbook that about 100 people in my organisation will 'own' a copy of, and be responsible for entering data into. In my workbook, I have a number of worksheets (3 sheets in total) that have merged cells where people will be entering in free-text. There could be any number of merged cells on each worksheet, each sheet is different. As merged cells, obviously they don't auto resize for height, and Im wanting to avoid 100 people messaging me asking for help on resizing the row (as I would say they are not particularly knowledgeable about excel in general).

I hope that clears my question up a bit. I am usually fairly good with excel but VBA codes are definitely a brand new thing to me!

Thanks

 

[6StringJazzer]

the VBA does still work when I manually hit run even though the code does not, as you say, make sense.

It's just not possible. I copied your code and pasted it into a sheet module and it will not run and will not even compile. As I predicted I get the error "Compile error: Expected End Sub". So the code you put in your post cannot be exactly what is in your file.

What I mean by the code not updating, I was under the impression that when you add a VBA code that this would run in the background of your document and make the required changes (maybe when you save the document, or change pages etc), but what is happening is that when I'm in the actual VBA code, I click run and I can see that the document is being updated, but unless i go into the code and hit run its not continuing to run and update in the background. Is that clearer? Maybe i am misunderstanding the way they work, in which case that would be good to know as well.

That is not how VBA works.

VBA can work in two ways.

Explicitly invoked by user action, like pressing a button, or "Run Macro". Something has to start the code running. It does not run in the background just because there is some code there.

Invoked by an event. Many actions taken by users trigger events, which VBA will respond to if you have a handler for that specific event. For example, you have a Sub called Worksheet_Activate. This is a special Sub that is a handler for the Worksheet.Activate event. Any time that the user activates that worksheet, VBA will automatically call that sub. (In your code it won't work because of the error I've mentioned above.) There are lots of other events, too many to discuss here. This may be what you are thinking of as "running in the background" but it still isn't quite the way you are thinking of it.

By the way, it would be easier to read your code if you use code tags. The forum takes out all leading spaces and extra spaces in a post, so the code loses its formatting. If you use code tags, the spacing is preserved, and long code is presented in a box with scroll bars:


[code]
' put your code here
[/code]