View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Bill Renaud Bill Renaud is offline
external usenet poster
 
Posts: 417
Default I want to format a worksheet based on values in another worksheet

I would revise your table slightly, as follows:

TabName Column Width Format Font Bold
1.csv A 10 mm/dd/yyyy Times New Roman TRUE
1.csv B 10 ="00." Times New Roman TRUE
2.csv A 8 ="00." Arial FALSE

If you are willing to do that, then you can try the following code. It
loops through each row in the table, then loops across each column, finds
the value, then uses a Select Case statement to decide what to do. If you
encounter any errors, just set a breakpoint at the bottom of the routine on
(or right after) the line label "ErrUnknownError:". Then check the Locals
window to what the variables are.

If you decide to add any additional columns, just make sure that the
TabName comes first, then the Column. After that, you can probably put
things in any order. Be sure to add code to the Select Case statement to
handle the new instruction (column on the "Drivers" worksheet).

Option Explicit

Const strMsgBoxTitle = "Format Data Worksheets"

'----------------------------------------------------------------------
Public Sub FormatDataWorksheets()
'Variables that all refer to the "Drivers" worksheet.
Dim wbData As Workbook 'Workbook with Formatting & Data.
Dim wsDriver As Worksheet 'Worksheet with formatting instructions.
Dim rngAttributes As Range 'Top row of Driver worksheet.
Dim rngAttribute As Range 'A single attribute on the top row.
Dim lngNumInstructions As Long 'Total rows of formatting instructions.
Dim lngInstruction As Long 'Single row of formatting instructions.
Dim varCell As Variant 'Single cell on the "Drivers" worksheet.

'Variables that refer to the data worksheet being formatted.
Dim wsData As Worksheet
Dim rngColumn As Range

Dim strErrorMsg As String

On Error GoTo ErrUnknownError

Application.ScreenUpdating = False

Set wbData = ActiveWorkbook
Set wsDriver = wbData.Worksheets("Driver")

With wsDriver.UsedRange
Set rngAttributes = .Resize(1) 'Top row of labels.
lngNumInstructions = .Rows.Count - 1 'Top row of labels doesn't count.
End With

For lngInstruction = 1 To lngNumInstructions
Set wsData = Nothing
Set rngColumn = Nothing

For Each rngAttribute In rngAttributes
varCell = wsDriver.Cells(lngInstruction + 1, rngAttribute.Column)

Select Case rngAttribute
Case "TabName"
Set wsData = wbData.Worksheets(varCell)
Case "Column"
Set rngColumn = wsData.Columns(varCell)
Case "Width"
rngColumn.ColumnWidth = varCell
Case "Format"
rngColumn.NumberFormat = varCell
Case "Font"
rngColumn.Font.Name = varCell
Case "Bold"
rngColumn.Font.Bold = varCell
Case Else
GoTo ErrUnkownAttribute
End Select
Next rngAttribute
Next lngInstruction

GoTo ExitSub

ErrUnkownAttribute:
strErrorMsg = "Unkown format attribute."
MsgBox strErrorMsg, vbCritical + vbOKOnly, strMsgBoxTitle
GoTo ExitSub

ErrUnknownError:
strErrorMsg = "Unknown error."
MsgBox strErrorMsg, vbCritical + vbOKOnly, strMsgBoxTitle
GoTo ExitSub

ExitSub:
End Sub

--
Regards,
Bill Renaud