VBA formating of Excel sheet when copying Access data

[Kemidan2014]

Good afternoon,

I have an onclick macro that uses a method of copying data from my form to an excel file using the following method

VBA opens an excel file template in a master folder

then the code uses the following type of coding to copy information into cells

wbTarget.worksheets("Report").Range("E6").Value = [QIMS#] 'field name we want copied into destination cell
wbTarget.worksheets("Report").Range("E6:H6").Merge 'Merges cell ranges
wbTarget.worksheets("Report").Range("E6:H6").HorizontalAlignment = xlCenter 'Center align

The only issue is sometimes depending on the length of characters I am copying or making equal to; the excel sheet row heights grow by alot forcing the user to resize.

is there another VBA code I can force a format to keep the row heights or column widths as original row height for the various cells I am writing to in this method? i would have shown pictures but they have already corrected the issue before i was able to post this

 

[welshgasman]

Use the autofit property of the range?

 

[Kemidan2014]

is this a property for the sheet or the cells? just needing to know if i would put that as a one liner some where at the top of the coding where i start that procedure or for each move

 

[welshgasman]

You would have to use excel automation to set the sheet properties.

 

[Micron]

Or is the problem that you don't want autofit because you want the row height to remain static? I don't know how to prevent it from happening, but you could first get a row height property of whatever range you want (range .RowHeight) then apply that value to the range .EntireRow.RowHeight where you need it to be that height value.

 

[Micron]

So something like this?

Dim dblHeight As Double
Dim sht As Worksheet
Dim rng As Range

Set sht = wbTarget.Worksheets("Report")
Set rng = sht.Range("E6:H6")

With sht
    dblHeight = .Range("A1").RowHeight 'choose the range that has the row height you want to keep
    .Range("E6") = [QIMS#] 'field name we want copied into destination cell
    With rng
        .Merge 'Merges cell ranges
        .HorizontalAlignment = xlCenter 'Center align
        .EntireRow.RowHeight = dblHeight
    End With
End With

I would choose one cell as the range to get the size from. If you used E6:H6 it could potentially have rows of different sizes so that could be a problem.
That is untested but AFAIK you can apply the row height for a range of rows. However, there may be a prompt raised so that would need handling.

 

[Micron]

If you used E6:H6 it could potentially have rows of different sizes

Nix that comment E6:H6 is one row, so that code can probably be simplified a bit if it's going to be of any value.