Adjust Row Height Macro - Not displaying correctly

[qiksho]

Hello,

I have some code that is supposed to adjust the row height to 13 for the first 200 rows as shown below:

Sub AdjustRowHeight(ByVal control As IRibbonControl)
Dim TargetWorksheet As Worksheet

For Each TargetWorksheet In Worksheets
Rows("1:200").RowHeight = 13
Next TargetWorksheet

End Sub

While this appears to work, it doesn't display as 13 when clicking on the Row Height divider on the left hand side of the screen. It shows as 12.75 instead. If I go into adjust Row Height it shows it is indeed 13. So while the code seems to work, it doesn't display the actual value correctly. Is this because it rounds to the nearest pixel value?

How can I make it show that is indeed 13 instead of 12.75 when hovering over the row divider?

Thanks in advance for any help!

 

[Norie]

Is that actually a problem?

Surely the main thing is that it's adjusting the row height.

Even when you do it manually via Format>Row... it comes out as 12.75.

By the way your code won't actually set the row height on every sheet - you aren't referencing the loop control variable when you refer to Rows.

That means the code will only operate on what VBA considers the active sheet.

It's easy to fix, try this.

TargetWorksheet.Rows("1:200").RowHeight = 13

 

[qiksho]

Hey Norie!

Thank you for the response. I did want it to modify every worksheet in the workbook, but was told it wasn't possible. Thank you for the fix!

The fact it shows as 12.75 instead of 13 will be an issue as this code is being assiged to a ribbon button for other users. For the limited user group it has been tested with some users were confused.

Any thoughts on how to make it show as 13? I know if you go into format Row Height it shows 13...

 

[Norie]

Who told you it wasn't possible?

Do the users really need to see the row height when they click the row divider?

If they do why?

 

[qiksho]

Who told you it wasn't possible?

Do the users really need to see the row height when they click the row divider?

If they do why?

One of the developers in our ITS department. In his defense I don't think he is very familiar with VBA.

This is a new "Firm Standard" row height on a couple documents. Many of the staff that will be using it are not going to go through training - just a communication of the new standard and tools available. The more intuitive it is the better the transition will be.

Many of our staff use the row divider to adjust row height. They will think the button to format the row doesn't work correctly since it doesn't display what it actually is....