Forum Discussion

EagleMoxy's avatar
EagleMoxy
Copper Contributor
Oct 18, 2023
Solved

Specific formatting in my excel table for OneDrive sharing. Possibly VBA needed.

Hello,

 

Thanks for looking at this. Appreciate all feedback both specific and general.

If there are any VBA champions here that might be what is needed or maybe more advanced functions.

I have formatted general things for my table, colours, conditional formatting, font size, but wanting a few more behavioral tweaks.

This sheet/table is currently shared via One Drive in a company environment.

Sheet linked to here is simplified version with some things missing.

pending ledger link

 

My wish list:


Position horizontal scrollbar to the left when sheet is opened in a browser tab or window, so the whole table shows.


Set default sheet zoom to 90% when first opened in browser (each time its opened in a new window or another tab in the browser) but allow adjusting after that initial load in the browser (not a locked attribute),


Date formatting to be Australian English region, 25/10/2023
DD/MM/YYYY force this format on all views if possible.

Block end user from shrinking table (removing rows). (I believe not allowing editing in sharing would cover this).

Block end user from Deleting rows or columns. (I believe not allowing editing in sharing would cover this).

 

 

In terms of field header, and column formatting.

 

* All columns have wrap text on.

* All column body text/data are size 10 font century gothic except where stated here.

* These settings are set up in the attached sheet, but I want to Bake them in (fixed formatting).

 

And as per the sheet sample are a mix of either center/middle aligned, or left/middle aligned.

 

If new data is being pasted into the cells, this sheets formatting should override the formatting from the data being pasted in from another sheet. Data types should remain fixed and non editable.

 

First contact and last contact headers

Header only: middle aligned to left.

Size 9 font.

 

Data type: date English (Australian)

 

- Column body text: size 9 font.

- Alignment; bottom align to right.

 

- English (Australian) format. date.

Job no. and dispatch headers

Header only: middle aligned to left.

Size 9 font

 

Data type: general. (not 'number' as there is some text involved).

 

 

- Column body text: size 8 font.

- Alignment: Bottom aligned and centered text.

Pending issue, response, and admin notes headers

Header only : middle align and centered.

Size 11 font.

 

Data type: text

- Column body text: size 10 font.

- Alignment: to Top, and to Left side margin.

 

- Font exception. user can apply BOLD and UNDERLINE, but not change font type or font size in columns.

 

- All cells in these 3 columns will add 1 extra line space at bottom of the cell when Enter is pressed to exit the cell.

*1* see graphic below table.

 

- Cell will always show all text entered. (Therefore the height of the specific row will be determined by which cell from these 3 headers has the most text, but if the cells are empty, fill with 3 blank lines spacing in the cell.

 

If that is too complicated, maintain visibility of other column cells. Meaning "first contact", "job no", "dispatch" cell data should be at least visible. (which would generally be 1 or 2 lines).

 

Status header

Header and column cells: middle align and centered.

Size 11 font.

 

Data type: text

 

Has own conditional formatting in the sheet.

I want this fixed/not editable by end user.

Body cell borders (not the headers).

no data type.

 

Apply to body cells, not headers.

As per the grey lines applied. (see sheet attached).

Agent header

Header only: Center and middle aligned.

Size 11 font.

 

Data type: text

 

- Column body text. font size: 10

- Alignment: Bottom and left aligned.

Site header

Header only: Center and middle aligned.

size 11 font

 

Data type: text

 

- Column body text: font size: 10

- alignment: Top and Left aligned

Issue header

Header only: middle and left aligned.

Size 11 font.

This will be a drop down menu option which will be fixed.

(not supplied here as yet).

 

- Column text: font size: 10

- alignment: Bottom and Left aligned

 

 

 

*1* from table.

Left and top aligned with 1 line space at bottom of cell for body text in pending issue, response, admin notes and Sites columns.

Border:

Border outlines are in the body cells, not the header.

 

Thanks for any advice, direction or assistance. Appreciated!.

Formulas and Functions
Macros and VBA
  • LeonPavesic's avatar
    LeonPavesic
    Oct 18, 2023

    Hi EagleMoxy,

     

    Here is a VBA code that you can use to format an Excel table for OneDrive sharing:

     

    Sub FormatTableForOneDrive()

'Declare variables
Dim ws As Worksheet
Dim tbl As Table
Dim rngHeader As Range
Dim rngBody As Range
Dim col As Column

'Set worksheet
Set ws = ActiveSheet

'Set table
Set tbl = ws.Tables("Table1")

'Format header row
Set rngHeader = tbl.HeaderRowRange
rngHeader.HorizontalAlignment = xlCenter
rngHeader.VerticalAlignment = xlCenter
rngHeader.Font.Size = 11
rngHeader.Font.Bold = True

'Format body rows
Set rngBody = tbl.Range("A2:" & tbl.Columns.Count)
rngBody.HorizontalAlignment = xlLeft
rngBody.VerticalAlignment = xlTop
rngBody.Font.Size = 10
rngBody.Font.Bold = False

'Format specific columns
'Pending Issue, Response, and Admin Notes columns
For Each col In tbl.Columns
If col.Name = "Pending Issue" Or col.Name = "Response" Or col.Name = "Admin Notes" Then
col.Cells.WrapText = True
col.Cells.VerticalAlignment = xlBottom
col.Cells.Font.Size = 9
End If
Next

'Status column
For Each col In tbl.Columns
If col.Name = "Status" Then
col.Cells.HorizontalAlignment = xlCenter
col.Cells.VerticalAlignment = xlCenter
col.Cells.Font.Size = 11
col.Cells.Font.Bold = True
End If
Next

'Apply borders to body cells
rngBody.Borders.LineStyle = xlContinuous
rngBody.Borders.ColorIndex = xlAutomatic
rngBody.Borders.Weight = xlThin

'Protect table
tbl.Protect

End Sub

     

     

    To use the VBA code:

    1. Open the Excel workbook that contains the table you want to format.
    2. Press Alt+F11 to open the Visual Basic Editor.
    3. Insert a new module by clicking Insert > Module.
    4. Paste the VBA code into the new module.
    5. Save the module and close the Visual Basic Editor.
    6. Back in the Excel workbook, select the table you want to format.
    7. Press Alt+F8 to open the Macro dialog box.
    8. Select the FormatTableForOneDrive macro and click Run.

    The macro will format the table with the settings specified in the code. The table will be protected so that users cannot change the formatting.

    Please click Mark as Best Response & Like if my post helped you to solve your issue.
    This will help others to find the correct solution easily. It also closes the item.


    If the post was useful in other ways, please consider giving it Like.


    Kindest regards,


    Leon Pavesic
    (LinkedIn)

  • LeonPavesic's avatar
    LeonPavesic
    Silver Contributor
    Oct 18, 2023

    Hi EagleMoxy,

     

    Here is a VBA code that you can use to format an Excel table for OneDrive sharing:

     

    Sub FormatTableForOneDrive()

'Declare variables
Dim ws As Worksheet
Dim tbl As Table
Dim rngHeader As Range
Dim rngBody As Range
Dim col As Column

'Set worksheet
Set ws = ActiveSheet

'Set table
Set tbl = ws.Tables("Table1")

'Format header row
Set rngHeader = tbl.HeaderRowRange
rngHeader.HorizontalAlignment = xlCenter
rngHeader.VerticalAlignment = xlCenter
rngHeader.Font.Size = 11
rngHeader.Font.Bold = True

'Format body rows
Set rngBody = tbl.Range("A2:" & tbl.Columns.Count)
rngBody.HorizontalAlignment = xlLeft
rngBody.VerticalAlignment = xlTop
rngBody.Font.Size = 10
rngBody.Font.Bold = False

'Format specific columns
'Pending Issue, Response, and Admin Notes columns
For Each col In tbl.Columns
If col.Name = "Pending Issue" Or col.Name = "Response" Or col.Name = "Admin Notes" Then
col.Cells.WrapText = True
col.Cells.VerticalAlignment = xlBottom
col.Cells.Font.Size = 9
End If
Next

'Status column
For Each col In tbl.Columns
If col.Name = "Status" Then
col.Cells.HorizontalAlignment = xlCenter
col.Cells.VerticalAlignment = xlCenter
col.Cells.Font.Size = 11
col.Cells.Font.Bold = True
End If
Next

'Apply borders to body cells
rngBody.Borders.LineStyle = xlContinuous
rngBody.Borders.ColorIndex = xlAutomatic
rngBody.Borders.Weight = xlThin

'Protect table
tbl.Protect

End Sub

     

     

    To use the VBA code:

    1. Open the Excel workbook that contains the table you want to format.
    2. Press Alt+F11 to open the Visual Basic Editor.
    3. Insert a new module by clicking Insert > Module.
    4. Paste the VBA code into the new module.
    5. Save the module and close the Visual Basic Editor.
    6. Back in the Excel workbook, select the table you want to format.
    7. Press Alt+F8 to open the Macro dialog box.
    8. Select the FormatTableForOneDrive macro and click Run.

    The macro will format the table with the settings specified in the code. The table will be protected so that users cannot change the formatting.

    Please click Mark as Best Response & Like if my post helped you to solve your issue.
    This will help others to find the correct solution easily. It also closes the item.


    If the post was useful in other ways, please consider giving it Like.


    Kindest regards,


    Leon Pavesic
    (LinkedIn)

    • EagleMoxy's avatar
      EagleMoxy
      Copper Contributor
      Oct 20, 2023

      LeonPavesicThank you so much, I really appreciate this, wonderful !!. Thanks for taking the time

Resources