How to adjust row height and column width with code

[maximas]

I'm trying to adjust the row and column with a macro, but let the user move the desire height and width. Because the sheet is lock, they can't use the mouse to do it, is there another way to do it.
ie.
Columns("D&quot.ColumnWidth = 9.71
rows("27:27&quot.RowHeight = 36.75

where("D&quot will be the selected column and ColumnWidth = the users move of the width
And Where ("27:27&quot is the selected row and RowHeight = the users move of the height with the mouse.

Please help!

 

[Dreamboat]

Very easiest way:

Open the VB Editor (Alt+F11), Double-click the sheet you want to put this macro button on and paste this macro into the code window:

Sub DoSettings()
Columns("D&quot.Select
Selection.ColumnWidth = 9.71
Rows("27:27&quot.Select
Selection.RowHeight = 36.75
Range("A1&quot.Select
End Sub


Then, Draw a textbox using the drawing toolbar, put something like "Click Here to Fix Row Heights and Column Widths" or whatever as the text. Fill it with a color and put a border on it.

Then, right-click the BORDER of the textbox and hit Assign Macro and choose the macro above that should now show in the list.

Anne Troy

http://www.TheOfficeExperts.com

 

[maximas]

Thanks, although the code and precedure looks great and it work for a specific row and column, but not for a general one. I meant that If I select a different cell, the code will be useless, not dynamically changing the selected row and column. the width and height is also static not changable. I need the number change according to the users mouse witha lock sheet.

 

[DaleWatson123321]

Hi maximus, and Dreamboat

Here's an "assist"...

The following routines will work on any ROW or COLUMN that the user selects.

I've attached these routines to keyboard shortcuts <Control> W for Column Width and <Control> H for Column Height

I've also created a named cell (&quot;incr&quot - for the end-user to be able to specify the &quot;increment&quot; for adjusting the Column Width or Row Height. Optionally, you could create two (separate) cells - one for Column Width, and one for Row Height.

Sub Set_ColumnWidth()
inc = Range(&quot;incr&quot.Value
curcolm = ActiveCell.Column
Columns(curcolm).Select
curwidth = Selection.ColumnWidth
Selection.ColumnWidth = curwidth + inc
End Sub

Sub Set_RowHeight()
inc = Range(&quot;incr&quot.Value
currow = ActiveCell.Row
Rows(currow).Select
curheight = Selection.RowHeight
Selection.RowHeight = curheight + inc
End Sub

Based on the above, the end-user can simply hold down the <Control> key and keep hitting the W (or H) to make whatever adjustment they want.

This will also work with NEGATIVE numbers - for REDUCING the Column Width or Row Height.

I hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Hi all,

Maximas,

I was a little too &quot;hasty&quot;. A couple of corrections...

1) My apologies for making a typo on your name.

2) Re my second sentence, where I refer to: &quot;<Control> W for Column Width and <Control> H for Column Height&quot;

This should have read: &quot;<Control> W for Column WIDTH and <Control> H for Row HEIGHT&quot;

An additional couple of points... You could place the cell named &quot;incr&quot; on a separate sheet and protect and hide that sheet (if you prefer). And you could have a macro button to prompt the end-user for a value to enter as the increment value. But if placing the &quot;incr&quot; cell on a protected sheet, you should &quot;Un-Lock&quot; that cell.

I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[PeterMoran]

Hi Dale,

I like your code - this is an area of Excel that has annoyed me in the past, and I have not as yet seen a satisfactory solution. It is not satisfactorily addressed by Excel.

Let me explain:

1. The numbers used in column width and row height are somewhat arbitrary and meaningless - whenever I have to change either, I always have to look at another cell to find or work out the number that I have to enter.

2. Generally speaking I don't want a number anyway, I virtually always want the default height or width, or maybe a multiple of that for row height. Consider Line Spacing in Word - you very rarely want to enter a number, its virtually always Single, 1.5, or Double spacing, with further options for entering a value.

So my ideal solution would be a submenu when either Row Height or Column Width was selected which contained the following options (for Row Height) - Same as row above, Default, Double, Triple, Enter a Value. And the similar arrangement for Column Width.

Regards,

Peter Moran