Free Microsoft Excel 2013 Quick Reference

Change type case Results

hey yall,
i am having some trouble with an Excel file that i have created. Basically it is a time sheet that calculates how much PTO (paid Time Off) each staff member earns / uses each pay period and shows an estimate of how much they should for any given date in the next year (excluding holidays and vacations that have been approved). It is set up in 7 columns...
Beginning Balance --- PTO Used --- PTO locked for future use --- Low Census --- Nonpaid Vacation Time --- Nonpaid Sick Time --- Ending Balance
What i am trying to do is add up
PTO Used --- PTO locked for future use --- Low Census --- Nonpaid Vacation Time --- Nonpaid Sick Time
and then subtract it from the total number of hours they are scheduled to work each pay period. I have some VBA coding in place that automatically formats what i type into the cells into [h]:mm ... The problem is that whenever i try to subtract the total of the inner columns from the scheduled hours it ends up being 0:02 or 0:03 or something instead of the actual total. It will add the numbers together just fine, but not subtract... To add to the confusion, it subtracts across the rows with no trouble at all. Here is the VBA coding in case you want to look at it and see if the problem lies in there somehow:

Code:
--------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

'Define the range where you want the code to work (our example is "C:G").
'Change within the " marks
If Intersect(Target, Range("H:Q")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
Select Case .Value
Case 0
.NumberFormat = "[h]:mm"
Case 1 To 99
.Value = TimeSerial(0, .Value, 0)
.NumberFormat = "[h]:mm"
Case 100 To 9999
.Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
.NumberFormat = "[h]:mm"
Case Else
End Select
End If
End With
errHandler:
Application.EnableEvents = True
End Sub

any help would be greatly appreciated... thanks

Could someone please help me figure out some way of printing a certain range over and over with one cell in that range changing each time it gets printed. Something like if cell A1 = 10 printrange "A2:D10" 10 times with cell A4 changing each time it gets printed from 1 to 10. Now the vba code says this...

Select Case Range("A1").Value
Case 1
Set r = Range("A2:D10")
Case 2
Set r = Range("A11:D20")

my problem comes when I have 20 of them I need to print...code gets to long. If there is a way to do this it would greatly decrease the amount of typing. Any suggestions would be greatly appreciated.

Thanks!

I have created quite a complex order form in excel using VB. Basically if an order code is selected it displays a certain set of parameters which then displays a certain set of parameter values which could be in a list could be a number between 1 and 100 or could be text. these are validation rules.

The problem i have is that if one of the params is group extensions which the user should type in the values 4444,4560,7842,4547 etc the validation rule is text between 1 and 255 chars. the cell format is general which messes up the list as it treats it as a number. I cannot explicity change this in excel because on a different order code there might be a different number format. how do i change it in vb.


	VB:
	
 range 
 
#        Case "Integer" 
**** here  i need To say Set cell format As number**** 
With parmValue.Validation 
    .Delete 
    .Add xlValidateWholeNumber, _ 
    xlValidAlertStop, _ 
    xlBetween, _ 
    parmIndex.Offset(0, 5).Value, _ 
    parmIndex.Offset(0, 6).Value 
    .InputTitle = parmDesc.Value 
    .InputMessage = parmIndex.Offset(0, 2).Value 
    .ErrorTitle = parmDesc.Value 
    .ErrorMessage = parmIndex.Offset(0, 3).Value 
    .IgnoreBlank = BlankAccept 
End With 
 
Case "Str-Int" 
    ******here i need To say Set cell format As text******* 
    With parmValue.Validation 
        .Delete 
        .Add xlValidateCustom, _ 
        xlValidAlertStop, _ 
        xlEqual, _ 
        "=OR(" & parmValue.Address & " = """ & _ 
        parmIndex.Offset(0, 5).Value & _ 
        """,AND(ISNUMBER(" & _ 
        parmValue.Address & ")," & _ 
        parmValue.Address & " >= " & _ 
        parmIndex.Offset(0, 6).Value & "," & _ 
        parmValue.Address & "

OS: XP Professional, Excel 2000.

The Excel Worksheet Change event does not generate a trigger when the value of a cell or a range of cells in the worksheet is changed by an external link. The example I am using is from John Walkenbach's Excel 2000 Power Programming with VBA, Chapter 18, Monitoring a specific range for changes; the code is as follows: Worksheet Change Event


	VB:
	
 Excel.Range) 
    Dim VRange As Range 
    Set Vrange =Range("InputRange") 
    For Each cell In Target 
        If Union(cell, Vrange).Address = Vrange.Address Then 
            Msgbox "The changed cell is in the input range." 
        End If 
    Next cell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
where InputRange is the range of cells monitored for changes. Each cell in the InputRange defines a DDE link to another application which pushes the data by using the DDE link syntax =application|topic!item.

The above-mentioned book states that the Worksheet Change event is triggered when the cells on a worksheet are changed by the user or an external link. In my case, when I type a new value in a cell of the InputRange, the change event is triggered and the message box pops up. However, when another application changes the value in such a cell via a DDE link declared using the above syntax, the event is not triggered.

I appreciate any solution or suggestion for solving the problem of triggering an event when the value of a cell in an Excel worksheet range is changed by an external application via a DDE link.

Thanks.

Hi

MyWorksheet: 4 columns: (format of the cells is General)
Columns A, B & C each contain either text or a number.
Column D, is a concatenate of A, B & C.

MyUserForm: 4 text or combo boxes:
cboA (input field for ColA) example input: Test
cboB (input field for ColB) example input: 1
txtC (input field for ColC) example input: 23
txtD (combines & displays on MyUserForm, the input of CboA, CboB & TxtC. Example result: Test123). Reason for this txtD on form is cosmetic 'read ease' for the User, but also utilising it as part of a 'duplicate check' procedure - as follows.

I have the following within my sub running from from the Userform's "OK" cmdButton.


	VB:
	
 Range 
 
strFind = txtD 
Set rLookRnge = Sheets("MyWorksheet").Columns(4) 
 
If Application.WorksheetFunction.CountIf(rLookRnge, strFind)  0 Then 
    MsgBox "Duplicate value exists", vbExclamation, "Duplicate Value" 
    cboA.SetFocus 'Change to suit
    Exit Sub 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

In other parts of the same sub , MyUserform's "OK" procedure (once all validition checks are done) happily takes the input from CboA, CboB & TxtC and places them on to the ws in ColA,B & C. And ColD on ws then happily concatenates them, to display Test123. The above part of the sub, is part of the validations & is intended to check for existing instances - duplicates - of, for example Test123 in the ws.

Problem:
I've run into trouble with the code above. meant to prevent duplicates of the 'value' displayed in MyUserForm.txtD from getting onto the ws into ColD.

Because - the duplicate check only works where data contained in ColD is **NOT** the result of a concatenate.
That is, if I manually type Test123 into any random cell in ColD, the code works (form msg fires, procedure stops and won't allow me to input another Test123 into that column.)

I guess this is because the concatenated value of ColD is not a String? Is there a way to change the code above so that the concatenated values of ColD are considered?

I do realise I could simply (I think!) have TxtD input into ColD and do away with the ws concatenate on ColD.
But I've inherited this worksheet as is, and in addition there is a certain elite level of User of the worksheet who may work directly on the ws, not via the userform (in which case I still need to have ColA,B & C concatenating into ColD .)

All help is much appreciated!
Thanks
Annie

So yesterday I got a nasty surprise when trying to run an automation I thought I had thoroughly debugged. The following code is a small fraction of the overall program, but it is where the error occurs. Before getting to this point, essentially all that has happened is that a .txt file has been imported and a few blank columns have been added to the left side to accomodate tags such as the one below. This snippet is supposed to fill column A with unique line numbers. So incredibly simple.

Private Sub PopulateLineNums()
'
' Written by Kelly Spencer
'
' Activates cell A1 and types in the number 1.
Range("A1").FormulaR1C1 = "1"
' Fills column A with a series from 1 with a stop value of LastRow
LastRow = Cells.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("A:A").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Stop:=LastRow, Trend:=False
'
End Sub

The problem lies in the LastRow = Cells.Find line. It causes an Overflow error (run-time #6). A little experimenting has led me to determine this has to do with file size. The macro runs fine on a file that is 32732 lines long, but kicks out the overflow error on yesterday's file of 34938 lines.

I have a hastily-scribbled note to myself from a month or so ago that there could be a problem with files longer than 32766 lines. I must have read this somewhere, but I didn't write down where, and I didn't write down what the problem would be. Doh.

So I've got a real dilemma now.

Ideally, I need to be able to process files longer than 32766 lines long. What fuctions, methods etc don't work on larger files? What work-arounds exist? Maybe I can change my code to avoid the pitfalls.

Barring that, I need code to be able to identify large imports early on (ie before import or immediately thereafter) so I can tell the user to chop up the .txt data file and reprocess it. In this case, a file this long would contain data for multiple mutual funds and could be broken down into individual funds if need be, but we'd rather process them all together, both to make the summary more meaningful and to save time and potential human error in only doing things once.

Lastly, that's all well and good while I have data files I can ask the user to break up, but we could run into a situation in the future where a single fund's data file is just that big.

I can't be the only one out there who works with large imports.

What do you all do?

Thanks,
Kelly.

Hi,

First of all, I know that merged cells are really frowned upon. However, this is one of those cases that cannot be solved with a pivot table (at least, I don't think it can). Here is what I need:

I am a teacher that uses an excel sheet that my school system gave me. It uses merged cells. I am trying to enhance the sheet so that it saves my colleagues and me time. Basically, I am making a sheet that will automatically create letters home to parents after each test. I've created drop-down menus at the top to change the data dynamically.

I need to dynamically reference merged cells from another sheet. This wouldn't be a problem, but it is referencing many sheets, and each sheet has a different merge situation. The good news is, it is limited to one row.

The code that I have isn't nearly where it should be. I ran into a wall when I tried to use a variable inside the merge command. I am very new to this type of stuff; thank you for your patience!

Here is the code I have so far:


	VB:
	
 
 
Public Sub MergeRow() 
    Dim c As Range 
    [a1:ac1].UnMerge '

I need to be able to get a macro to do this. I recorded the macro. The conditional worked, but when I cleared it and then ran the macro, it highlighted all of the wrong stuff.

Here is what I want to do:
In column(AJ) I have cities with Prefixes or not In another column (AV) I have Criteria1 and in yet another, I have criteria2 (CB)

City
(AJ) Criteria1 Criteria2
SCRNRTH_SCRM1 FALSE TRUE should fail test
A9_SCRNRTH_SCRM1 FALSE TRUE
A9_SCRNRTH_SCRM1 FALSE TRUE
A9_SCRNRTH_SCRM1 FALSE FALSE should fail test
SCRNRTH_SCRM1 TRUE TRUE should fail test
A9_SCRNRTH_SCRM1 FALSE TRUE
SCRNRTH_SCRM1 FALSE FALSE
A9_SCRNRTH_SCRM1 FALSE FALSE should fail test
A9_SCRNRTH_SCRM1 FALSE FALSE should fail test
A9_SCRNRTH_SCRM1 TRUE TRUE should fail test
A9_SCRNRTH_SCRM1 FALSE TRUE

What I need to do is to format an ERROR alert (red fill with white letters) any error conditions.
In this case, IF Cities start with A9_ AND Criteria1 =TRUE, then Criteria MUST be false, otherwise, if cities start with A9_, Criteria should be TRUE
If Cities does NOT have A9_, then Criteria2 should be false

here is the FORMULA that works if I put it in a different column, it will report TRUE if it is valid, and false if it is wrong

=IF(CB3,IF(AV3,IF(LEFT(AJ3,3)="A9_",FALSE,TRUE),TRUE),TRUE)

Here is what the MACRO recorded

	VB:
	
 CF_PNP() 
     '
     ' CF_PNP Macro
     '
     
     '
    Range("CD2:CD8500").Select 
     '   Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _ 
    Formula1:="=IF(LEFT(AJ3,3)=""A9_"",TRUE,FALSE) = TRUE" 
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
    With Selection.FormatConditions(1).Font 
        .Bold = True 
        .Italic = True 
        .ThemeColor = xlThemeColorDark1 
        .TintAndShade = 0 
    End With 
    With Selection.FormatConditions(1).Interior 
        .PatternColorIndex = xlAutomatic 
        .Color = 255 
        .TintAndShade = 0 
    End With 
    Selection.FormatConditions(1).StopIfTrue = False 
End Sub 
Sub CF_CallHold() 
     '
     ' CF_CallHold Macro
     '
     
    Range("CB3:CB8425").Select 
     '    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ 
    Formula1:="=FALSE" 
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
     
    Selection.FormatConditions(1).StopIfTrue = True ' Changed to stop if true
     
     
     
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ 
    Formula1:="=IF(CB3,IF(AV3,IF(LEFT(AJ3,3)=""A9_"",FALSE,TRUE),TRUE),TRUE)" 
     '   Formula1:="=IF(CB3,IF(AV3,IF(LEFT(AJ3,3)=""A9_"",FALSE,TRUE),TRUE),TRUE)"
     
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
    With Selection.FormatConditions(1).Font 
        .ThemeColor = xlThemeColorDark1 
        .TintAndShade = 0 
    End With 
    With Selection.FormatConditions(1).Interior 
        .PatternColorIndex = xlAutomatic 
        .Color = 255 
        .TintAndShade = 0 
    End With 
    Selection.FormatConditions(1).StopIfTrue = False 
    ActiveWindow.SmallScroll Down:=0 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

How could I correct this to get the desired results? I have tried to recreate this again in the wizard, but I have not be able to get it working

Thanks
Mc

Hi,
I've been trying to shade a cell (A2) a certain color based on the value in another cell (B2), but I can't seem to get there with the code I've pieced together from other code snippets.

I have a table of cities ("contacts200805_table") where the city cells are color coded based on which person is assigned to work in that city. People are often reassigned to different cities, so I'm trying to create a macro that will update the cell color for me.

I use a different software to generate the list of people and their assigned cities. I copy the list into a table ("input_contacts"). The city info populates A2:A1043. The person's name populates B2:B1043. "Input_contacts" and "contacts200805" have the exact same cities and always will. The people will change. I use the "input contacts" table to just dump the new list. The "contacts200805" table is what I use for the report, so the format can't be changed.

Here is what I want my macro to do:
start macro
select the "input contacts" table
copy the people in range B2:B118
select the "contacts200805" table
paste the people in B2:B118 which is next to the city column (A2:A118)
repeat for other columns

look at the name in cell B2 on the "contacts200805" table
if name = jane doe then color the city cell (A2) red
if name= john hanson then color city cell green, etc. (I have 6 people to compare)
repeat check for all cells that contain a persons name (columns B, D, F, H, J, L, N, P & R)

make the person's name text color white so it doesn't show up on the final printout
end macro

When I run the macro below I get a 'type mismatch' error at the first case select


	VB:
	
 Contacts_Click() 
     'select the input_contacts sheet and select the a set of contacts and copy
     'go to contacts200805 table and paste the contacts into the column next to the city column
     
    Sheets("input_contacts").Select 
    Range("B2:B118").Select 
    Selection.Copy 
    Sheets("contacts200805_table").Select 
    Range("B2:B118").Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
     
    Sheets("input_contacts").Select 
    Range("B119:B235").Select 
    Selection.Copy 
    Sheets("contacts200805_table").Select 
    Range("D2:D118").Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
     
    Sheets("input_contacts").Select 
    Range("B236:B352").Select 
    Selection.Copy 
    Sheets("contacts200805_table").Select 
    Range("F2:F118").Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
     
    Sheets("input_contacts").Select 
    Range("B353:B469").Select 
    Selection.Copy 
    Sheets("contacts200805_table").Select 
    Range("H2:H118").Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
     
    Sheets("input_contacts").Select 
    Range("B470:B586").Select 
    Selection.Copy 
    Sheets("contacts200805_table").Select 
    Range("J2:J118").Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
     
    Sheets("input_contacts").Select 
    Range("B587:B703").Select 
    Selection.Copy 
    Sheets("contacts200805_table").Select 
    Range("L2:L118").Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
     
    Sheets("input_contacts").Select 
    Range("B704:B820").Select 
    Selection.Copy 
    Sheets("contacts200805_table").Select 
    Range("N2:N118").Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
     
    Sheets("input_contacts").Select 
    Range("B821:B937").Select 
    Selection.Copy 
    Sheets("contacts200805_table").Select 
    Range("P2:P118").Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
     
    Sheets("input_contacts").Select 
    Range("B938:B1043").Select 
    Selection.Copy 
    Sheets("contacts200805_table").Select 
    Range("R2:R107").Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
     
     'conditional formatting
     'look at the contact names in columns B, D, F, H, J, L, N, P, R and if they match any
     'of the case values then color the city’s cell to the left of the name a certain color
     'brown=53
     'tan=40
     'sea green=50
     'pink=7
     'gold=44
     'rose=38
     'light yellow=36
     
    Select Case Range("B2:B118").Value 
    Case "Jane Doe" 
        Range("B2").Offset(0, -1).Interior.ColorIndex = 7 
    Case "John Hanson" 
        Range("B2").Offset(0, -1).Interior.ColorIndex = 44 
    Case "Charlie Boone" 
        Range("B2").Offset(0, -1).Interior.ColorIndex = 38 
    Case "Jack Grough" 
        Range("B2").Offset(0, -1).Interior.ColorIndex = 36 
    Case "Linda May" 
        Range("B2").Offset(0, -1).Interior.ColorIndex = 53 
    Case "Dan Bee" 
        Range("B2").Offset(0, -1).Interior.ColorIndex = 40 
    Case "Dave Gill" 
        Range("B2").Offset(0, -1).Interior.ColorIndex = 50 
    Case Else 
        Range("B2").Offset(0, -1).Interior.ColorIndex = 255 
    End Select 
     
     'make the text in the columns between the city columns white
    Range("B2:B118").Select 
    Selection.Font.ColorIndex = 2 
    Range("D2:D118").Select 
    Selection.Font.ColorIndex = 2 
    Range("F2:F118").Select 
    Selection.Font.ColorIndex = 2 
    Range("H2:H118").Select 
    Selection.Font.ColorIndex = 2 
    Range("J2:J118").Select 
    Selection.Font.ColorIndex = 2 
    Range("L2:L118").Select 
    Selection.Font.ColorIndex = 2 
    Range("N2:N118").Select 
    Selection.Font.ColorIndex = 2 
    Range("P2:P118").Select 
    Selection.Font.ColorIndex = 2 
    Range("R2:R118").Select 
    Selection.Font.ColorIndex = 2 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I've also attached sample xls file. The colors and names may not match up correctly because I changed the names for the sample.

Any suggestions is greatly appreaciated,
Charlotte

Big apologies for the inane post, but I'm a bigtime noob with VBA, struggling to do simple stuff. In this case, I'm trying to get around the three-condition limit in conditional format, adding one more condition in VBA (the other three conditions similarly change a cell's color based on, you guessed it, the appearance of the name of the color in the cell.

The problem is running this macro only works for the active sheet, and I'd like it to run for all 40 sheets in my workbook. I've tried adding "For Each Sheet..."-type statements to no avail. Here's the code:

	VB:
	
 ChangeColor() 
    Dim Cell As Range 
    For Each Cell In Range("E6:E37") 
         
        If Cell.Value = "Gray" Then 
            Cell.Interior.ColorIndex = 15 
             
        End If 
         
    Next Cell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
THANKS.

Hey,

I have a spreadsheet thats split into two tabs.

One is a sort of Tracker sheet, where we input an Address for a customer.

The other sheet is an Associated address sheet.

Each address on the Tracker sheet has an unique case ID, I.e 001

What I want is if we type 001 on the 'Associated' sheet, then it will auto populate the address fields on that sheet, based on the other sheet.

I tried a VLOOKUP in terms of changing the column number and naming the range of case id's =vlookup(a2,Case,4) but it just came up with a #n/a

Do you have an idea's of a work around?

Thanks,

Steve

Hello, I'm using VBA to produce a combo box for any cell using data validation list.

This way the cell auto completes as well as displays as many fields as desired...I now have it functioning so that when you click on any cell that contains data validation list assigned to it, it will create the combo box in the cell for you...It functions great most of the time, however the sheet is used all day long and on a average 10 hour day it causes Excel to crash about 3-5 times.

The only major change I made is changing:


	VB:
	
  Range, _ 
    Cancel As Boolean) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
To:


	VB:
	
 Range) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The reason I did this was to enable the script to execute by just clicking on any cell using data validation list...I'm guessing this is where my problem is coming from.

Here is the full script for reference:


	VB:
	
 Range) 
    Dim str As String 
    Dim cboTemp As OLEObject 
    Dim ws As Worksheet 
    Dim wsList As Worksheet 
    Set ws = ActiveSheet 
    Set wsList = Sheets("Rep Assist Report") 
     
    Cancel = True 
    Set cboTemp = ws.OLEObjects("RepName1") 
    On Error Resume Next 
    With cboTemp 
        .ListFillRange = "" 
        .LinkedCell = "" 
        .Visible = False 
    End With 
    On Error Goto errHandler 
    If Target.Validation.Type = 3 Then 
        Application.EnableEvents = False 
        str = Target.Validation.Formula1 
        str = Right(str, Len(str) - 1) 
        With cboTemp 
            .Visible = True 
            .Left = Target.Left 
            .Top = Target.Top 
            .Width = Target.Width + 5 
            .Height = Target.Height + 5 
            .ListFillRange = str 
            .LinkedCell = Target.Address 
        End With 
        cboTemp.Activate 
    End If 
     
errHandler: 
    Application.EnableEvents = True 
    Exit Sub 
End Sub 
Private Sub Worksheet(ByVal Target As Range) 
    Dim str As String 
    Dim cboTemp As OLEObject 
    Dim ws As Worksheet 
    Set ws = ActiveSheet 
    Application.EnableEvents = False 
    Application.ScreenUpdating = True 
     
    Set cboTemp = ws.OLEObjects("RepName1") 
    On Error Resume Next 
    With cboTemp 
        .Top = 10 
        .Left = 10 
        .Width = 0 
        .ListFillRange = "" 
        .LinkedCell = "" 
        .Visible = False 
        .Value = "" 
    End With 
     
errHandler: 
    Application.EnableEvents = True 
    Exit Sub 
End Sub 
Private Sub RepName1_KeyDown(ByVal _ 
    KeyCode As MSForms.ReturnInteger, _ 
    ByVal Shift As Integer) 
    Select Case KeyCode 
    Case 9 
        ActiveCell.Offset(0, 1).Activate 
    Case 13 
        ActiveCell.Offset(1, 0).Activate 
    Case Else 
    End Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As always, any help is much appreciated!
Chris

Hey there, I'm using a script that when you double click on a cell containing Data Validation > List it changes to a combo box that has an autocomplete/increased columns view...It's working great, however there are two small modifications I would love to do and am not having much success with.

Currently, you double click the cell w/the Data Validation > List in it, it then changes to a combo box and you can auto complete using the keyboard or select the dropdown and choose from an increased list.

However, I would like to be able to simply single click on the cell, have it change to the combo box and auto expand the list if possible.

Here is my code:


	VB:
	
 Range, _ 
    Cancel As Boolean) 
    Dim str As String 
    Dim cboTemp As OLEObject 
    Dim ws As Worksheet 
    Dim wsList As Worksheet 
    Set ws = ActiveSheet 
    Set wsList = Sheets("Rep Assist Report") 
     
    Cancel = True 
    Set cboTemp = ws.OLEObjects("RepName1") 
    On Error Resume Next 
    With cboTemp 
        .ListFillRange = "" 
        .LinkedCell = "" 
        .Visible = False 
    End With 
    On Error Goto errHandler 
    If Target.Validation.Type = 3 Then 
        Application.EnableEvents = False 
        str = Target.Validation.Formula1 
        str = Right(str, Len(str) - 1) 
        With cboTemp 
            .Visible = True 
            .Left = Target.Left 
            .Top = Target.Top 
            .Width = Target.Width + 5 
            .Height = Target.Height + 5 
            .ListFillRange = str 
            .LinkedCell = Target.Address 
        End With 
        cboTemp.Activate 
    End If 
     
errHandler: 
    Application.EnableEvents = True 
    Exit Sub 
End Sub 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    Dim str As String 
    Dim cboTemp As OLEObject 
    Dim ws As Worksheet 
    Set ws = ActiveSheet 
    Application.EnableEvents = False 
    Application.ScreenUpdating = True 
     
    Set cboTemp = ws.OLEObjects("RepName1") 
    On Error Resume Next 
    With cboTemp 
        .Top = 10 
        .Left = 10 
        .Width = 0 
        .ListFillRange = "" 
        .LinkedCell = "" 
        .Visible = False 
        .Value = "" 
    End With 
     
errHandler: 
    Application.EnableEvents = True 
    Exit Sub 
End Sub 
Private Sub RepName1_KeyDown(ByVal _ 
    KeyCode As MSForms.ReturnInteger, _ 
    ByVal Shift As Integer) 
    Select Case KeyCode 
    Case 9 
        ActiveCell.Offset(0, 2).Activate 
    Case 13 
        ActiveCell.Offset(0, 2).Activate 
    Case Else 
    End Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance!
PHRoG

Not quite as easy as it sounds I'm afraid as its a multiple conditional format and thus needs coding. At the moment I'm using the following code:


	VB:
	
 Range) 
     ' Conditional Formatting for more than 3 conditions
    Dim rng As Range 
     ' Target is a range::therefore,it can be more than one cell
     ' For example,,someone could delete the contents of a range,
     ' or someone could enter an array..
    Set rng = Intersect(Target,
Range("E27:E34,G27:G34,I27:I34,K27:K34,M27:M34,O27:O34,Q27:Q34,S27:S34,U27:U34,W27:W34,Y27:Y34,AA27:AA34")) 
     
    If rng Is Nothing Then 
        Exit Sub 
    Else 
         
        Select Case rng.Value 
        Case 0 
            rng.Interior.Color = RGB(255, 255, 255) 
            rng.Font.Color = RGB(0, 0, 0) 
        Case 1 
            rng.Interior.Color = RGB(230, 230, 230) 
            rng.Font.Color = RGB(0, 0, 0) 
        Case 2 
            rng.Interior.Color = RGB(204, 204, 204) 
            rng.Font.Color = RGB(0, 0, 0) 
        Case 3 
            rng.Interior.Color = RGB(179, 179, 179) 
            rng.Font.Color = RGB(0, 0, 0) 
        Case 4 
            rng.Interior.Color = RGB(153, 153, 153) 
            rng.Font.Color = RGB(0, 0, 0) 
        Case 5 
            rng.Interior.Color = RGB(128, 128, 128) 
            rng.Font.Color = RGB(0, 0, 0) 
        Case 6 
            rng.Interior.Color = RGB(102, 102, 102) 
            rng.Font.Color = RGB(0, 0, 0) 
        Case 7 
            rng.Interior.Color = RGB(77, 77, 77) 
            rng.Font.Color = RGB(255, 255, 255) 
        Case 8 
            rng.Interior.Color = RGB(51, 51, 51) 
            rng.Font.Color = RGB(255, 255, 255) 
        Case 9 
            rng.Interior.Color = RGB(26, 26, 26) 
            rng.Font.Color = RGB(255, 255, 255) 
        Case 10 
            rng.Interior.Color = RGB(0, 0, 0) 
            rng.Font.Color = RGB(255, 255, 255) 
        Case Else 
            rng.Interior.Color = RGB(128, 128, 128) 
            rng.Font.Color = RGB(0, 0, 0) 
        End Select 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This works very nicely, so that for example if I type the number 10 into cell M33, that cells fill colour becomes black (and the text turns white). Alas I would now also like to extend the fill and text colour to the cell one to the left too. To take this example I would type 10 into cell M33 and cells L33 and M33 would both be filled black and their text would be white.

I'm not sure if this requires a change to the code, or if its possible to just have a formula or something which copies the fomatting of one cell into another, but any help is much appreciated.

Many Thanks

George

Hello!

Is there any way to make a macro go through several rows without ruining the functionality? When I change the range it will only allow it to check on box on the whole sheet, not one per section (2), per line, like I would like.

The macro currently looks like this:


	VB:
	
 
If Not Intersect(Target, Range("O8:R8")) Is Nothing Then 
    Target.Font.Name = "Tahoma" 
    If Target = vbNullString Then 
        If WorksheetFunction.CountA(Range("O8:R8"))  0 Then 
            MsgBox "You may check only one box per case!  Base your choice on the most serious outcome for that case.",
vbCritical 
        Else 
            Target = "X" 
        End If 
    Else 
        Target = vbNullString 
    End If 
End If 
 
 'Tick on Click & Restrict Tick Cell Upon Selection To 1 Tick Per Row - Injury/Illness Type Zone
If Target.Cells.Count > 1 Then Exit Sub 
If Not Intersect(Target, Range("U8:Z8")) Is Nothing Then 
    Target.Font.Name = "Tahoma" 
    If Target = vbNullString Then 
        If WorksheetFunction.CountA(Range("U8:Z8"))  0 Then 
            MsgBox "You may check only one box per case!  Check the 'Injury' column or choose one type of illness.",
vbCritical 
        Else 
            Target = "X" 
        End If 
    Else 
        Target = vbNullString 
    End If 
End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So, is there any easy way (i.e. not writing out the formula for each row) to make this macro go all the way down to line 28?

Thanks!

I have a Workbook that I have set a macro to e-mail to our network access team and save to a specific folder on our network drive based upon the value of a combo box that contains all of our site locations.
I keep getting a 'variable not defined' error for my combobox.value. I have tried to assign a variable that is equal to the combobox.value but that has no effect. I appreciate the help in advance.

This is the code to add the values to the combo box:

	VB:
	
 Workbook_Open() 
    With Sheet1 
        .bxLocation.Value = "Select Site" 
        .bxLocation.List = Array("MH-Mt. Hope", "SV-Summersville", "HU-Huntington", "NA-Saltville", "PL-Pulaski",
"KV-Kingsville", "OD-Odessa", "WL-Wheeling", "FL-Ft. Lauderdale", "Other") 
        .bxFormType.Value = "Form Type" 
        .bxFormType.List = Array("Add", "Change", "Remove") 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This is the code to start the macro

	VB:
	
 cmdSendSave_Click() 
     
    Call SendSave 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This is the module

	VB:
	
 SendSave() 
     
    Dim strDir          As String 
    Dim strNewFolder    As String 
    Dim strMonth        As String 
    Dim strDirLong      As String 
    Dim strDate         As String 
    Dim strUser         As String 
    Dim strOffice       As String 
    Dim strForm         As String 
     
    strDir = "mthopeVol1AccessRequests" 
    strMonth = Month(Now) 
    strUser = Range("F12").Value 
    strForm = bxFormType.Value 
     
    If Len(strMonth) = 1 Then 
        strMonth = "0" & strMonth 
    End If 
     
    strNewFolder = strMonth & "-" & Year(Now) 
    strDate = Format(Date, "dd/mmm/yy") 
     
     
    Select Case bxLocation.Value 
    Case "MH-Mt. Hope": strDirLong = strDir & "MH" 
        If Len(Dir(strDirLong & strNewFolder, vbDirectory)) = 0 Then 
            MkDir (strDirLong & strNewFolder) 
        End If 
        strDirLong = strDirLong & strNewFolder 
        .Range("F15").Value = "Mt. Hope" 
         
    Case "SV-Summersville": strDirLong = strDir & "SV" 
        If Len(Dir(strDirLong & strNewFolder, vbDirectory)) = 0 Then 
            MkDir (strDirLong & strNewFolder) 
        End If 
        strDirLong = strDirLong & strNewFolder 
        .Range("F15").Value = "Summersville" 
         
    Case "HU-Huntington": strDirLong = strDir & "HU" 
        If Len(Dir(strDirLong & strNewFolder, vbDirectory)) = 0 Then 
            MkDir (strDirLong & strNewFolder) 
        End If 
        strDirLong = strDirLong & strNewFolder 
        .Range("F15").Value = "Huntington" 
         
    Case "PL-Pulaski": strDirLong = strDir & "NRV" 
        If Len(Dir(strDirLong & strNewFolder, vbDirectory)) = 0 Then 
            MkDir (strDirLong & strNewFolder) 
        End If 
        strDirLong = strDirLong & strNewFolder 
        .Range("F15").Value = "Pulaski" 
         
    Case "NA-Saltville": strDirLong = strDir & "NA" 
        If Len(Dir(strDirLong & strNewFolder, vbDirectory)) = 0 Then 
            MkDir (strDirLong & strNewFolder) 
        End If 
        strDirLong = strDirLong & strNewFolder 
        .Range("F15").Value = "Saltville" 
         
    Case "KV-Kingsville": strDirLong = strDir & "KV" 
        If Len(Dir(strDirLong & strNewFolder, vbDirectory)) = 0 Then 
            MkDir (strDirLong & strNewFolder) 
        End If 
        strDirLong = strDirLong & strNewFolder 
        .Range("F15").Value = "Kingsville" 
         
    Case "OD-Odessa": strDirLong = strDir & "OD" 
        If Len(Dir(strDirLong & strNewFolder, vbDirectory)) = 0 Then 
            MkDir (strDirLong & strNewFolder) 
        End If 
        strDirLong = strDirLong & strNewFolder 
        .Range("F15").Value = "Odessa" 
         
    Case "WL-Wheeling": strDirLong = strDir & "WL" 
        If Len(Dir(strDirLong & strNewFolder, vbDirectory)) = 0 Then 
            MkDir (strDirLong & strNewFolder) 
        End If 
        strDirLong = strDirLong & strNewFolder 
        .Range("F15").Value = "Wheeling" 
    Case "FL-Ft.Lauderdale": strDirLong = strDir & "FL" 
        If Len(Dir(strDirLong & strNewFolder, vbDirectory)) = 0 Then 
            MkDir (strDirLong & strNewFolder) 
        End If 
        strDirLong = strDirLong & strNewFolder 
        .Range("F15").Value = "Ft. Lauderdale" 
         
    Case "Other": strDirLong = strDir & "Remote" 
        If Len(Dir(strDirLong & strNewFolder, vbDirectory)) = 0 Then 
            MkDir (strDirLong & strNewFolder) 
        End If 
        strDirLong = strDirLong & strNewFolder 
        .Range("F15").Value = "Remote" 
    End Select 
     
     
     
    With ActiveBook 
        .Title = "User Access Request Form: " & strUser 
        .SaveAs Filename:=strDirLong & strUser & ".xls" 
    End With 
     
    Application.ScreenUpdating = False 
    cmdSendSave.Delete 
     
    Set OutApp = CreateObject("Outlook.Application") 
    Set OutMail = OutApp.CreateItem(0) 
     
    With OutMail 
        .To = "4tech.services@gcsagents.com" 
        .CC = "" 
        .BCC = "" 
        .Subject = "User Access Maintenance Request" 
        .HTMLBody = SheetToHTML(ActiveSheet) 
        .Send 'or use .Display
    End With 
     
    Set OutMail = Nothing 
    Set OutApp = Nothing 
End Sub 
 
 
Public Function SheetToHTML(sh As Worksheet) 
     'Function from Dick Kusleika his site
     'http://www.dicks-clicks.com/excel/sheettohtml.htm
     'Changed by Ron de Bruin 19-Aug-2006
    Dim TempFile As String 
    Dim Nwb As Workbook 
    Dim fso As Object 
    Dim ts As Object 
     
    sh.Copy 
    Set Nwb = ActiveWorkbook 
     
    With Nwb.Sheets(1) 
        On Error Resume Next 
        .DrawingObjects.Visible = True 
        .DrawingObjects.Delete 
        On Error Goto 0 
    End With 
     
     
    TempFile = Environ$("temp") & "/" & _ 
    Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 
     
    Nwb.SaveAs TempFile, xlHtml 
    Nwb.Close False 
     
    Set fso = CreateObject("Scripting.FileSystemObject") 
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) 
    SheetToHTML = ts.ReadAll 
    ts.Close 
     
    On Error Resume Next 
    Kill TempFile 
    fso.deletefolder Left(TempFile, Len(TempFile) - 4) & "*", True 
    On Error Goto 0 
     
    Set ts = Nothing 
    Set fso = Nothing 
    Set Nwb = Nothing 
     
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi all

I have a VBA issue that I would love some help with.

Ok.

I have an excel sheet which allows a user to input a path to a specific file which is then used to build a pivot table. There are three different sections. First the user selects where the input file is, then they select where they would like to save the output file and then the click 'GO' which runs the below code:


	VB:
	
 ' Description:  Executes when the user clicks the Go button.
 '               Checks if an input file has been specifed and if so opens it to a sheet.
 '               Calls other methods to create a summary pivot table and save the file.
 ' Parameters:   None
 ' Returns:      Nothing
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      Andrew Foster - 15 March 2006.
 '               Added Sheet Protection
 ' ToDo:
 
Sub OpenFile_Click() 
    Sheets("Process Sheet").Unprotect 
     'Get file name of file to open
    Dim strOpenFilePath As String 
    strOpenFilePath = Range("InputFile").Value 
     
     'Get file name of output file
    Dim strOutputFilePath As String 
    strOutputFilePath = Range("OutputFile").Value 
     
     'Check output file name valid
    If (strOpenFilePath = "") Then 
        MsgBox ("No file specified") 
    Else 
        On Error Goto ErrorHandler 
         
         'Get just the file name
        Dim lngPos As Long 
        Dim strOpenFile As String 
        lngPos = InStrRev(strOpenFilePath, "") 
        strOpenFile = Right$(strOpenFilePath, Len(strOpenFilePath) - lngPos) 
         
         'Opens the .txt file and appends the correct column header.
        Call openAndFormatTxt(strOpenFilePath) 
         
         'Delete the last row
        Set rgLast = Range("A1").SpecialCells(xlCellTypeLastCell) 
        lLastRow = rgLast.Row 
        Rows(lLastRow).Select 
        Selection.ClearContents 
        Range("A1").Select 
         
         'Modify amounts for reversals
        Call ModifyAmountsForReversals 
         
         'Format Time column
        Call FormatTime 
         
         'Sort results by Date then time
        Call SortResults 
         
         'Create summary pivot table
        Call PivotTable(strOpenFilePath) 
         
         'Save output file
        If (strOutputFilePath = "") Then 
            MsgBox ("Unable to Save output, invalid output file name.") 
        Else 
            Call SaveOutput(strOutputFilePath) 
            Workbooks("ANZEftposMacro.xls").Activate 
            Sheets("Process Sheet").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True 
            Exit Sub 
        End If 
         
    End If 
    Workbooks("ANZEftposMacro").Activate 
    Sheets("Process Sheet").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True 
     
    Exit Sub 
     
ErrorHandler: 
    MsgBox ("Error opening file.  " + Err.Description) 
     
End Sub 
 
 ' Method name:  ModifyAmountsForReversals()
 ' Description:  Adds an extra column and recalucates the Amounts in cents.
 '               Amount will become negative if the transaction ID is zero (0).
 '               This recalculated value is copied to the original column
 ' Parameters:   None
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      None
 ' ToDo:         None
Private Sub ModifyAmountsForReversals() 
     
     'Move to the temporary column
    Range("F1").Select 
    Selection.End(xlDown).Select 
    ActiveCell.Offset(0, 12).Select 
     
     'Create formula for recalculation and apply to temporary column
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]=0, (RC[-12]*-1)/100, RC[-12]/100)" 
    ActiveCell.Copy 
    Range("R2", ActiveCell).Select 
    ActiveSheet.Paste 
     
     'Copy over new values to previous Amount column
    Selection.Copy 
    Range("F1").Select 
    Selection.End(xlDown).Select 
    Range("F2", ActiveCell).PasteSpecial Paste:=xlPasteValues 
    Application.CutCopyMode = False 
     
     'Remove Temporary column contents
    Columns("R:R").Select 
    Selection.ClearContents 
    Range("A1").Select 
     
     
     'Format Amount Column
    Columns("F:F").Select 
    Selection.NumberFormat = "$#,##0.00" 
     
End Sub 
 
 ' Method name:  FormatTime()
 ' Description:  Formats the Time field by appending ":" between the time components.
 '
 ' Parameters:   None
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      None
 ' ToDo:         None
Private Sub FormatTime() 
     
     'Change format of temporary column
    Range("R:R").Select 
    Selection.NumberFormat = "h:mm:ss AM/PM" 
     
     'Move to the temporary column
    Range("F1").Select 
    Selection.End(xlDown).Select 
    ActiveCell.Offset(0, 12).Select 
     
     
     'Create formula for reformat and apply to temporary column
    ActiveCell.FormulaR1C1 = _ 
    "=if(LEN(RC[-14])=6 , Time(MID(RC[-14], 1, 2), MID(RC[-14], 3, 2), MID(RC[-14], 5, 2)), Time(MID(RC[-14], 1, 1),
MID(RC[-14], 2, 2), MID(RC[-14], 4, 2)))" 
     '"=IF(LEN(RC[-14])=6, CONCATENATE(MID(RC[-14], 1, 2), "":"", MID(RC[-14], 3, 2), "":"", MID(RC[-14], 5, 2)),
CONCATENATE(CONCATENATE(""0"",MID(RC[-14], 1, 1)), "":"", MID(RC[-14], 2, 2), "":"", MID(RC[-14], 4, 2)))"
     
    ActiveCell.Select 
    ActiveCell.Copy 
    Range("R2", ActiveCell).Select 
    ActiveSheet.Paste 
     
     'Copy over new values to previous Time column
    Selection.Copy 
    Range("D1").Select 
    Selection.End(xlDown).Select 
    Range("D2", ActiveCell).PasteSpecial Paste:=xlPasteValues 
    Application.CutCopyMode = False 
     
     'Remove Temporary column contents
    Columns("R:R").Select 
    Selection.ClearContents 
     
     'Change format of Time column
    Range("D:D").Select 
    Selection.NumberFormat = "h:mm:ss AM/PM" 
     
    Range("C1").Select 
     
End Sub 
 
 ' Method name:  SortResults()
 ' Description:  Sorts the results by date then time
 ' Parameters:   None
 ' Returns:      Nothing
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      None
 ' ToDo:         None
Private Sub SortResults() 
    Cells.Select 
    Range("C1").Activate 
    Selection.CurrentRegion.Select 
    Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("D2") _ 
    , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ 
    False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ 
    :=xlSortNormal 
     
    Range("C1").Select 
End Sub 
 
 
 ' Method name:  PivotTable()
 ' Description:  A pivot table is created using the given file source on another sheet
 '               summing the total cents against the date.
 ' Parameters:   file    the input file name
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      None
 ' ToDo:         Double check column headers
 
Private Sub PivotTable(file As String) 
     
     'Displays a pivot table on a new sheet using the data from the text file.
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
    file + "!R1C1:R1000C16").CreatePivotTable TableDestination:="", TableName _ 
    :="PivotTable2", DefaultVersion:=xlPivotTableVersion10 
     
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) 
    ActiveSheet.Cells(3, 1).Select 
    ActiveWorkbook.ShowPivotTableFieldList = True 
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Tran Date") 
        .Orientation = xlRowField 
        .Position = 1 
    End With 
     
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable2").PivotFields("Amount"), "Sum of Amount", xlSum 
     
    Application.CommandBars("PivotTable").Visible = False 
    ActiveWorkbook.ShowPivotTableFieldList = False 
     
     'Format cells
    Range("B5:B8").Select 
    Selection.NumberFormat = "$#,##0.00" 
    Columns("B:B").EntireColumn.AutoFit 
    Range("B9").Select 
     
End Sub 
 
 ' Method name:  SaveOutput()
 ' Description:  Saves the workbook to the specified file name.
 ' Parameters:   file    the filepath to save the workbook to.
 ' Returns:      Nothing
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      None
 ' ToDo:         Double check column headers
 
Private Sub SaveOutput(file As String) 
    On Error Goto ErrorHandler 
    ActiveWorkbook.SaveAs Filename:=file, _ 
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ 
    ReadOnlyRecommended:=False, CreateBackup:=False 
     
    Exit Sub 
ErrorHandler: 
    If (Err.Number  1004) Then 
        MsgBox ("Error saving file.  " + CStr(Err.Number) + CStr(Err.Source) + Err.Description) 
    End If 
End Sub 
 
 ' Method name:  openAndFormatTxt()
 ' Description:  Opens the input text file and puts data into columns.
 '               Formats the sheet by adding relevant column headers,
 '               resizing some columns and hiding unnecessary columns.
 ' Parameters:   strOpenFile     the input file to open
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      None
 ' ToDo:         None
Private Sub openAndFormatTxt(strOpenFile As String) 
     
     'Open input file
    Workbooks.OpenText Filename:=strOpenFile, Origin:=xlMSDOS, _ 
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(1, _ 
    1), Array(5, 5), Array(13, 1), Array(19, 1), Array(25, 1), Array(37, 1), Array(43, 2), Array _ 
    (53, 9), Array(56, 1), Array(62, 1), Array(67, 1), Array(74, 9), Array(80, 1), Array(92, 9), _ 
    Array(96, 1), Array(97, 9), Array(98, 1), Array(101, 2), Array(117, 9), Array(120, 1), _ 
    Array(128, 1)), TrailingMinusNumbers:=True 
     
     'Append column headers
    ActiveCell.FormulaR1C1 = "Rec ID" 
    Range("B1").Select 
    ActiveCell.FormulaR1C1 = "Message Type" 
    Range("C1").Select 
    ActiveCell.FormulaR1C1 = "Tran Date" 
    Range("D1").Select 
    ActiveCell.FormulaR1C1 = "Tran Time" 
    Range("E1").Select 
    ActiveCell.FormulaR1C1 = "Tran Code" 
    Range("F1").Select 
    ActiveCell.FormulaR1C1 = "Amount" 
    Range("G1").Select 
    ActiveCell.FormulaR1C1 = "PAN" 
    Range("H1").Select 
    ActiveCell.FormulaR1C1 = "Mobile Number" 
    Range("I1").Select 
    ActiveCell.FormulaR1C1 = "Sequence Number" 
    Range("J1").Select 
    ActiveCell.FormulaR1C1 = "Network" 
    Range("K1").Select 
    ActiveCell.FormulaR1C1 = "Retailer ID" 
    Range("L1").Select 
    ActiveCell.FormulaR1C1 = "Terminal ID" 
    Range("M1").Select 
    ActiveCell.FormulaR1C1 = "Responder" 
    Range("N1").Select 
    ActiveCell.FormulaR1C1 = "Response Code" 
    Range("O1").Select 
    ActiveCell.FormulaR1C1 = "Card No" 
    Range("P1").Select 
    ActiveCell.FormulaR1C1 = "Vodafone Trans ID" 
     
     'Bold font the column header
    Rows("1:1").Select 
    Range("C1").Activate 
    Selection.Font.Bold = True 
     
     'Resize columns
    Columns("C:C").Select 
    Columns("C:C").EntireColumn.AutoFit 
    Columns("F:F").EntireColumn.AutoFit 
    Columns("K:K").EntireColumn.AutoFit 
    Columns("P:P").EntireColumn.AutoFit 
    Columns("O:O").EntireColumn.AutoFit 
    Columns("L:L").EntireColumn.AutoFit 
    Columns("H:H").Select 
    Columns("H:H").EntireColumn.AutoFit 
     
     'Hide unnecessary columns
    Range("A1").Select 
    Range("A:A,B:B,E:E,G:G,I:I,J:J,L:L,M:M,N:N").Select 
    Range("N1").Activate 
    Selection.EntireColumn.Hidden = True 
    ActiveWindow.ScrollColumn = 3 
     
     'Format Date column
    Columns("C:C").Select 
    Selection.NumberFormat = "d/mm/yyyy;@" 
     
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

This file/macro runs fine when executed on my machine. When i upload the file to a portal on a different server, it throws the following error:

Error opening file. Method 'OpenText' of object 'Workbooks' failed

This error is from the error handler in the above code.

It appears as though it fails opening the file in the folowing sub:


	VB:
	
 ' Description:  Opens the input text file and puts data into columns.
 '               Formats the sheet by adding relevant column headers,
 '               resizing some columns and hiding unnecessary columns.
 ' Parameters:   strOpenFile     the input file to open
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      None
 ' ToDo:         None
Private Sub openAndFormatTxt(strOpenFile As String) 
     
     'Open input file
    Workbooks.OpenText Filename:=strOpenFile, Origin:=xlMSDOS, _ 
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(1, _ 
    1), Array(5, 5), Array(13, 1), Array(19, 1), Array(25, 1), Array(37, 1), Array(43, 2), Array _ 
    (53, 9), Array(56, 1), Array(62, 1), Array(67, 1), Array(74, 9), Array(80, 1), Array(92, 9), _ 
    Array(96, 1), Array(97, 9), Array(98, 1), Array(101, 2), Array(117, 9), Array(120, 1), _ 
    Array(128, 1)), TrailingMinusNumbers:=True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I hover above strOpenFilePath it has the correct value i.e. file location "C:TempVOIS0830.TXT"

This is the path that I selected in step 1: Selecting the input file.

Another thing to note is that I have tried putting a copy of the same file in the same folder on the server just in case it was having difficulties finding the file...it still produced the same error.

This problem is driving me crazy, so any assistance would be very appreciated.

Thank you.

I have included the other two macro code in case it is required:

Find File:


	VB:
	
 ' Description:  Executes when the user clicks the Find File button.
 '               Dialogue opens and user selects the input file for the Macro
 '               Selected file is populated to cell E5. User is able to cancel the operation
 '               Using the same file path, a default output file location is shown in E9
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      Andrew Foster - 15 March 2006.
 '               Added Sheet Protection
 ' ToDo:         None
 
Sub FindFile_Click() 
     
    Dim sFileToLoad As Variant 
     
    On Error Goto ErrorHandler 
     
    Sheets("Process Sheet").Unprotect 
     
    sFileToLoad = Application.GetOpenFilename(FileFilter:= _ 
    "Text Files (*.txt), *.txt,Microsoft Office Excel Workbook (*.xls),*.xls,All Files (*.*),*.*.") 
     
    If sFileToLoad  False Then 
        Range("InputFile").Value = (CStr(sFileToLoad)) 
         'Add a default filename for the output
        Dim lngPos As Long 
        Dim strDefaultOutputFile As String 
        lngPos = InStrRev(CStr(sFileToLoad), ".") 
        strDefaultOutputFile = Left$(CStr(sFileToLoad), lngPos - 1) + "Output.xls" 
        Range("OutputFile").Value = CStr(strDefaultOutputFile) 
    End If 
    Sheets("Process Sheet").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True 
    Exit Sub 
     
     
ErrorHandler: 
    MsgBox ("Error locating file.  " + Err.Description) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Save File:


	VB:
	
 ' Description:  Executes when the user clicks the Save Location button.
 '               Dialogue opens and user selects where to save the output of the Macro
 '               Selected file is populated to cell E9. User is able to cancel the operation
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      Andrew Foster - 15 March 2006.
 '               Added Sheet Protection
 ' ToDo:         None
 
Sub btnSave_Click() 
     
    Dim sFileToSave As Variant 
     
    On Error Goto ErrorHandler 
     
    Sheets("Process Sheet").Unprotect 
     
    sFileToSave = Application.GetSaveAsFilename(InitialFileName:="", _ 
    FileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls,Text Files (*.txt), *.txt,All Files (*.*),*.*.") 
    If sFileToSave  False Then 
        Range("OutputFile").Value = (CStr(sFileToSave)) 
    End If 
    Sheets("Process Sheet").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True 
    Exit Sub 
     
ErrorHandler: 
    MsgBox ("Error locating save destination.  " + Err.Description) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi!

I have a spreadsheet with a variable number of xlButtonControl type form controls. The actual number of buttons depends on the amount of data that the spreadsheet is being asked to summarise. All the button controls use the ".OnAction" property to point execution on click to a common procedure.

Unfortunately that much is what it is and is beyond my control, so I just have to work with that.

What I would like to know is if there is a property or method I can access from within the common OnAction procedure that enable me to identify which button was pressed. If this was a CommandBar environment I would use the ActionControl property. Since it's not, and since I can't change it (not even to use ActiveX controls!), does anyone know an equivalent that I can use in this case?

Cheers

MDL

Im currently making a schuduler for a series of machines to run off of. The top of the spreadsheet has the day broken up into its many hours and 15 minute increments. The bottom of the spreadsheet has a table. When orders are placed they are automatically entered into the table from another spreadsheet and assigned a random color. A coordinator can then type a runtime into the table at the bottom and the scheduler will fill in the hours with that products color (which will actually be a series of numbers in cells to match the colors numbers).

Where Im stuck at is I have a series of colors that have been assigned numbers. The problem is if a coordinator filled in a time and then decided to change it or delete what they had it has a runtime error, unless they delete the cells one at a time. The real documents time table runs from C4:CT22. I have linked a dumbed down version so you can see what I mean. If you try to delete 5 cells of 3's at a time it has the error.

I was hoping somebody could think of a solution so one would be able to delete areas of color because Im afraid if the coordinators have to see VBA or runtime errors they will freak, so it needs to be simple. I tried creating a macro to find specific cells with the numbers and delete them but that crashed excel everytime I ran it or even just tried to do it through edit>find then deleting them.

color code

	VB:
	
 Range) 
    Dim icolor As Integer 
     
    If Not Intersect(Target, Range("C4:N8")) Is Nothing Then 
        Select Case Target 
        Case 3 
            icolor = 3 
        Case 4 
            icolor = 4 
        Case 6 
            icolor = 6 
        Case 7 
            icolor = 7 
        Case 8 
            icolor = 8 
        Case 10 
            icolor = 10 
        Case Else 
        End Select 
         
        Target.Interior.ColorIndex = icolor 
    End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
any help or thoughts would be appreciated, THANKS

denkinge

I have this very long macro that approximately 6 people use. Each time I update this macro with changes I have to send each person the new excel sheet and copy and paste all the data from the new macro to there macro workbook (ie. personal.xls). This is very time consuming. I work for a large company so everything is identified by our login #'s. I could put this data on one of our servers but Im not sure what I need to do to make this a program they all can run. Each day they create a new workbook and paste a bunch of data on it. Then they run my macro from there personal macro workbook. Does anyone have any ideas on how I could set this up so I would not have to update there workbooks each time I make a change? Here is my macro..

	VB:
	
 
 
Sub DiscListMacro() 
    Cells.Select 
    Application.CutCopyMode = False 
    Selection.sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
     
    Dim rSearch As Range, cl As Range 
     
    Set rSearch = Range("J1", Range("J65536").End(xlUp)) 
     
    For Each cl In rSearch 
        Select Case cl.Value 
        Case "PSSNAP" 
            Cells(cl.Row, 17).Value = "Sales" 
        Case "1999" 
            Cells(cl.Row, 17).Value = "Plug" 
        Case "2XXX" 
            Cells(cl.Row, 17).Value = "Plug" 
             
        End Select 
    Next cl 
    Set rSearch = Range("N1", Range("N65536").End(xlUp)) 
     
    For Each cl In rSearch 
        Select Case cl.Value 
        Case "PSSNAP" 
            Cells(cl.Row, 17).Value = "Sales" 
        Case "1999" 
            Cells(cl.Row, 17).Value = "Plug" 
        Case "2XXX" 
            Cells(cl.Row, 17).Value = "Plug" 
             
        End Select 
    Next cl 
    Set rSearch = Range("L1", Range("L65536").End(xlUp)) 
     
    For Each cl In rSearch 
        Select Case cl.Value 
        Case ("2669") 
            Cells(cl.Row, 17).Value = "Warning" 
        Case "2091" 
            Cells(cl.Row, 17).Value = "Warning" 
        Case "2086" 
            Cells(cl.Row, 17).Value = "Warning" 
        Case "2642" 
            Cells(cl.Row, 17).Value = "Warning" 
        Case "2270" 
            Cells(cl.Row, 17).Value = "Warning" 
        Case "2034" 
            Cells(cl.Row, 17).Value = "Warning" 
        Case "2900" 
            Cells(cl.Row, 17).Value = "Warning" 
        Case "8013" 
            Cells(cl.Row, 17).Value = "Warning" 
        Case "8010" 
            Cells(cl.Row, 17).Value = "Warning" 
        Case "2036" 
            Cells(cl.Row, 17).Value = "Warning" 
        Case "PSSNAP" 
            Cells(cl.Row, 17).Value = "Sales" 
        Case "1999" 
            Cells(cl.Row, 17).Value = "Plug" 
        Case "2XXX" 
            Cells(cl.Row, 17).Value = "Plug" 
        Case "2699" 
            Cells(cl.Row, 17).Value = "Warning" 
        Case "4004" 
            Cells(cl.Row, 17).Value = "Warning" 
             
        End Select 
    Next cl 
     
     
     
     
    Set rSearch = Range("F1", Range("F65536").End(xlUp)) 
     
    For Each cl In rSearch 
        Select Case cl.Value 
        Case "11" 
            If Mid(Cells(cl.Row, 3), 16, 1)  "7" Then Cells(cl.Row, 17).Value = "See Trans 11 List" 
        End Select 
    Next cl 
     
     
     
     
    Set rSearch = Range("J1", Range("J65536").End(xlUp)) 
     
    For Each cl In rSearch 
        If CStr(Left(cl.Value, 4)) = "WARN" Then Cells(cl.Row, 17).Value = "Warning" 
         
    Next cl 
    Set rSearch = Range("L1", Range("L65536").End(xlUp)) 
     
    For Each cl In rSearch 
        If CStr(Left(cl.Value, 4)) = "WARN" Then Cells(cl.Row, 17).Value = "Warning" 
         
    Next cl 
    For Each cl In rSearch 
        If CStr(Left(cl.Value, 4)) = "NOTE" Then Cells(cl.Row, 17).Value = "Warning" 
         
    Next cl 
    Set rSearch = Range("C1", Range("C65536").End(xlUp)) 
    For Each cl In rSearch 
        If CStr(Left(cl.Value, 1)) = "A" And CStr(Left(cl.Offset(0, 5).Value, 2)) = "32" Then cl.Offset(0, 14).Value =
"Offshore" 
         
    Next cl 
    Set rSearch = Range("C1", Range("C65536").End(xlUp)) 
    For Each cl In rSearch 
        If CStr(Left(cl.Value, 1)) = "A" And CStr(Left(cl.Offset(0, 5).Value, 2)) = "42" Then cl.Offset(0, 14).Value =
"Offshore" 
         
    Next cl 
    Set rSearch = Range("Q1", Range("Q65536").End(xlUp)) 
     
    For Each cl In rSearch 
        Select Case cl.Value 
        Case "Warning" 
            Cells(cl.Row, 18).Value = "Warning" 
        Case "Sales" 
            Cells(cl.Row, 18).Value = "Sales" 
        Case "Offshore" 
            Cells(cl.Row, 18).Value = "Offshore" 
        Case "Plug" 
            Cells(cl.Row, 18).Value = "Plug" 
        Case "See Trans 11 List" 
            Cells(cl.Row, 18).Value = "See Trans 11 List" 
             
             
        End Select 
    Next cl 
    Set rSearch = Range("Q1", Range("Q65536").End(xlUp)) 
     
    For Each cl In rSearch 
        Select Case cl.Value 
        Case "Warning" 
            Cells(cl.Row, 19).Value = Cells(cl.Row, 2).Value 
        Case "Sales" 
            Cells(cl.Row, 19).Value = Cells(cl.Row, 2).Value 
        Case "Offshore" 
            Cells(cl.Row, 19).Value = Cells(cl.Row, 2).Value 
        Case "Plug" 
            Cells(cl.Row, 19).Value = Cells(cl.Row, 2).Value 
        Case "See Trans 11 List" 
            Cells(cl.Row, 19).Value = Cells(cl.Row, 2).Value 
        End Select 
    Next cl 
    Set rSearch = Range("Q1", Range("Q65536").End(xlUp)) 
     
    For Each cl In rSearch 
        Select Case cl.Value 
        Case "Warning" 
            Cells(cl.Row, 20).Value = "No Action Needed" 
        Case "Sales" 
            Cells(cl.Row, 20).Value = "No Action Needed" 
        Case "Offshore" 
            Cells(cl.Row, 20).Value = "No Action Needed" 
        Case "Plug" 
            Cells(cl.Row, 20).Value = "No Action Needed" 
        Case "See Trans 11 List" 
            Cells(cl.Row, 20).Value = "No Action Needed" 
             
        End Select 
    Next cl 
    Range("V2").Select 
    ActiveCell.FormulaR1C1 = "=IF(RC[-21]>0,1,"""")" 
    Range("V2").Select 
    Selection.AutoFill Destination:=Range("V2:V9000"), Type:=xlFillDefault 
    Range("V2:V9000").Select 
    Range("V9000").Select 
     
    Range("W2").Select 
    ActiveCell.FormulaR1C1 = _ 
   
"=IF(RC[-6]=""y"",1,IF(RC[-6]=""Warning"",1,IF(RC[-6]=""Sales"",1,IF(RC[-6]=""Offshore"",1,IF(RC[-6]=""Plug"",1,IF(RC[-6]=""See
Trans 11 List"",1,""""))))))" 
    Range("W2").Select 
    Selection.AutoFill Destination:=Range("W2:W9000"), Type:=xlFillDefault 
    Range("W2:W3000").Select 
    Range("W3000").Select 
    Range("Z2").Select 
    ActiveCell.FormulaR1C1 = "=RC[-24]" 
    Selection.AutoFill Destination:=Range("Z2:Z6"), Type:=xlFillDefault 
    Range("Z2:Z6").Select 
    Range("Z6").Select 
    Selection.Copy 
    Range("Z8").Select 
    ActiveSheet.Paste 
    Range("AA2").Select 
    ActiveCell.FormulaR1C1 = "=SUMIF(R2C1:R9000C1,2,R2C22:R9000C22)" 
    Range("AA3").Select 
    ActiveCell.FormulaR1C1 = "=SUMIF(R2C1:R9000C1,5,R2C22:R2C22:R9000C22)" 
    Range("AA4").Select 
    ActiveCell.FormulaR1C1 = "=SUMIF(R2C1:R9000C1,6,R2C22:R9000C22)" 
    Range("AA5").Select 
    ActiveCell.FormulaR1C1 = "=SUMIF(R2C1:R9000C1,8,R2C22:R9000C22)" 
    Range("AA6").Select 
    ActiveCell.FormulaR1C1 = "=SUMIF(R2C1:R9000C1,9,R2C22:R9000C22)" 
    Range("AA8").Select 
    ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-2]C)" 
    Range("AB2").Select 
    ActiveCell.FormulaR1C1 = "=SUMIF(R2C1:R9000C1,2,R2C23:R9000C23)" 
    Range("AB3").Select 
    ActiveCell.FormulaR1C1 = "=SUMIF(R2C1:R9000C1,5,R2C23:R9000C23)" 
    Range("AB4").Select 
    ActiveCell.FormulaR1C1 = "=SUMIF(R2C1:R9000C1,6,R2C23:R9000C23)" 
    Range("AB5").Select 
    ActiveCell.FormulaR1C1 = "=SUMIF(R2C1:R9000C1,8,R2C23:R[8995]C23)" 
    Range("AB6").Select 
    ActiveCell.FormulaR1C1 = "=SUMIF(R2C1:R9000C1,9,R2C23:R9000C23)" 
    Range("AB8").Select 
    ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-2]C)" 
    Range("AC2").Select 
    ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/RC[-2])" 
    Range("AC2").Select 
    Selection.AutoFill Destination:=Range("AC2:AC6"), Type:=xlFillDefault 
    Range("AC2:AC6").Select 
    Range("AC8").Select 
    ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/RC[-2])" 
    Range("AD2").Select 
    ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-2])" 
    Range("AD3").Select 
    ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-2])" 
    Range("AD4").Select 
    ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-2])" 
    Range("AD5").Select 
    ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-2])" 
    Range("AD6").Select 
    ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-2])" 
    Range("AD8").Select 
    ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-2])" 
    Range("AE2").Select 
    ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/RC[-4])" 
    Range("AE2").Select 
    Selection.AutoFill Destination:=Range("AE2:AE6"), Type:=xlFillDefault 
    Range("AE2:AE6").Select 
    Range("AE8").Select 
    ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/RC[-4])" 
    Range("Q1").Select 
    ActiveCell.FormulaR1C1 = "Review Completed (Y/N)" 
    Range("R1").Select 
    ActiveCell.FormulaR1C1 = "Completed By" 
    Range("S1").Select 
    ActiveCell.FormulaR1C1 = "Date Completed" 
    Range("T1").Select 
    ActiveCell.FormulaR1C1 = "Action Taken" 
    Range("V1").Select 
    ActiveCell.FormulaR1C1 = "Total Counts" 
    Range("W1").Select 
    ActiveCell.FormulaR1C1 = "Review Counts" 
    Range("Y1").Select 
    ActiveCell.FormulaR1C1 = "Region" 
    Range("Z1").Select 
    ActiveCell.FormulaR1C1 = "Date" 
    Range("AA1:AB1").Select 
    ActiveCell.FormulaR1C1 = "Total Errors" 
    Range("AB1").Select 
    ActiveCell.FormulaR1C1 = "Total Reviewed" 
    Range("AC1").Select 
    ActiveCell.FormulaR1C1 = "% Reviewed" 
    Range("AD1").Select 
    ActiveCell.FormulaR1C1 = "Total Not Reviewed" 
    Range("AE1").Select 
    ActiveCell.FormulaR1C1 = "% Not Reviewed" 
    Range("Y2").Select 
    ActiveCell.FormulaR1C1 = "2" 
    Range("Y3").Select 
    ActiveCell.FormulaR1C1 = "5" 
    Range("Y4").Select 
    ActiveCell.FormulaR1C1 = "6" 
    Range("Y5").Select 
    ActiveCell.FormulaR1C1 = "8" 
    Range("Y6").Select 
    ActiveCell.FormulaR1C1 = "9" 
    Range("Y8").Select 
    ActiveCell.FormulaR1C1 = "Total" 
    Range("Y1:AE8").Select 
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
    With Selection.Borders(xlEdgeLeft) 
        .LineStyle = xlContinuous 
        .Weight = xlThin 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Borders(xlEdgeTop) 
        .LineStyle = xlContinuous 
        .Weight = xlThin 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Borders(xlEdgeBottom) 
        .LineStyle = xlContinuous 
        .Weight = xlThin 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Borders(xlEdgeRight) 
        .LineStyle = xlContinuous 
        .Weight = xlThin 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Borders(xlInsideVertical) 
        .LineStyle = xlContinuous 
        .Weight = xlThin 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Borders(xlInsideHorizontal) 
        .LineStyle = xlContinuous 
        .Weight = xlThin 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Interior 
        .ColorIndex = 15 
        .Pattern = xlSolid 
        .PatternColorIndex = xlAutomatic 
    End With 
    Selection.Font.Bold = True 
    With Selection.Font 
        .Name = "Arial" 
        .Size = 10 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ColorIndex = xlAutomatic 
    End With 
    Columns("Q:W").Select 
    Range("W1").Activate 
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
    With Selection.Borders(xlEdgeLeft) 
        .LineStyle = xlContinuous 
        .Weight = xlThin 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Borders(xlEdgeTop) 
        .LineStyle = xlContinuous 
        .Weight = xlThin 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Borders(xlEdgeBottom) 
        .LineStyle = xlContinuous 
        .Weight = xlThin 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Borders(xlEdgeRight) 
        .LineStyle = xlContinuous 
        .Weight = xlThin 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Borders(xlInsideVertical) 
        .LineStyle = xlContinuous 
        .Weight = xlThin 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Borders(xlInsideHorizontal) 
        .LineStyle = xlContinuous 
        .Weight = xlThin 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Interior 
        .ColorIndex = 15 
        .Pattern = xlSolid 
        .PatternColorIndex = xlAutomatic 
    End With 
    Rows("1:1").Select 
    Range("S1").Activate 
    Selection.Font.Bold = True 
    Cells.Select 
    Range("S1").Activate 
    Cells.EntireColumn.AutoFit 
    Columns("U:U").Select 
    Selection.Interior.ColorIndex = 1 
    Selection.ColumnWidth = 2.57 
    Cells.Select 
    Selection.AutoFilter 
    Range("Q2").Select 
    Columns("Q:Q").ColumnWidth = 16.43 
    Rows("1:1").Select 
    Range("Q1").Activate 
    With Selection 
        .HorizontalAlignment = xlGeneral 
        .VerticalAlignment = xlBottom 
        .WrapText = True 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
    End With 
    Columns("Q:Q").ColumnWidth = 14.29 
    Columns("Q:Q").ColumnWidth = 13.29 
    Columns("Q:Q").ColumnWidth = 15.86 
    Rows("1:1").Select 
    Columns("Q:AE").Select 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlBottom 
        .WrapText = True 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
    End With 
    Columns("Q:Q").ColumnWidth = 15.57 
    Columns("Q:Q").ColumnWidth = 15.14 
    Columns("Q:Q").ColumnWidth = 14.14 
    Columns("Q:Q").ColumnWidth = 13.57 
    Rows("1:1").EntireRow.AutoFit 
    Range("R1").Select 
    Columns("R:R").ColumnWidth = 12 
    Columns("S:S").ColumnWidth = 13.57 
    Columns("T:T").ColumnWidth = 11 
    Columns("T:T").ColumnWidth = 13.71 
    Columns("T:T").ColumnWidth = 15.57 
    Columns("T:T").ColumnWidth = 18.29 
    Columns("T:T").ColumnWidth = 19.71 
    ActiveWindow.ScrollColumn = 18 
    ActiveWindow.ScrollColumn = 19 
    Columns("V:W").Select 
    Selection.EntireColumn.Hidden = True 
    Columns("Y:Y").ColumnWidth = 10# 
    Columns("AE:AE").ColumnWidth = 14 
    Columns("AD:AD").ColumnWidth = 17.29 
    Columns("AD:AD").ColumnWidth = 15.57 
    Columns("AD:AD").ColumnWidth = 13.43 
    Columns("AC:AC").ColumnWidth = 10# 
    Columns("AB:AB").ColumnWidth = 13.43 
    Columns("AA:AA").ColumnWidth = 10.14 
    Columns("S:S").Select 
    Selection.NumberFormat = "m/d/yyyy" 
    Range("AC2:AC8").Select 
    Selection.NumberFormat = "0%" 
    Range("AE2:AE8").Select 
    Selection.NumberFormat = "0%" 
    Range("Y7:AE7").Select 
    Selection.Interior.ColorIndex = xlNone 
    Cells.Select 
    Range("R1").Activate 
    Selection.AutoFilter 
    ActiveWorkbook.WritePassword = "quality" 
    ActiveWorkbook.ReadOnlyRecommended = True 
    Cells.Select 
    Range("R1").Activate 
    Selection.AutoFilter 
    Range("U1").Select 
    ActiveCell.FormulaR1C1 = "1" 
    Range("U2").Select 
    Range("AZ2").Select 
    ActiveCell.FormulaR1C1 = "Automation Tool" 
    Range("AZ3").Select 
    ActiveCell.FormulaR1C1 = "Forward For Completion" 
    Range("AZ4").Select 
    ActiveCell.FormulaR1C1 = "No Action Needed" 
    Range("AZ5").Select 
    ActiveCell.FormulaR1C1 = "Offshore" 
    Range("AZ6").Select 
    ActiveCell.FormulaR1C1 = "Other" 
    Range("AZ7").Select 
    ActiveCell.FormulaR1C1 = "Produce Manual" 
    Range("AZ8").Select 
    ActiveCell.FormulaR1C1 = "Reinput" 
    Range("AZ9").Select 
    ActiveCell.FormulaR1C1 = "Reported as Problem" 
    Range("AZ10").Select 
    ActiveCell.FormulaR1C1 = "Review in Alternate System" 
    Range("AZ11").Select 
    ActiveCell.FormulaR1C1 = "Systems Action" 
    Range("AZ12").Select 
    ActiveCell.FormulaR1C1 = "Too Late For Action" 
    Range("AZ13").Select 
    ActiveCell.FormulaR1C1 = "Verified Change" 
    Range("AZ14").Select 
    Range("T2:T65000").Select 
    With Selection.Validation 
        .Delete 
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
        xlBetween, Formula1:="=$AZ$2:$AZ$13" 
        .IgnoreBlank = True 
        .InCellDropdown = True 
        .InputTitle = "" 
        .ErrorTitle = "Incorrect Data Entered" 
        .InputMessage = "" 
        .ErrorMessage = "Please select option from drop down menu." 
        .ShowInput = True 
        .ShowError = True 
    End With 
    Columns("AZ:AZ").Select 
    Selection.EntireColumn.Hidden = True 
    Range("A1").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines