Excel VBA - Add new columns with text and formatti

问题:

I am working on an Excel macro (button) that will add a column into the same spot in multiple worksheets. Also, this column must have a column header that is input through a dialog box.

Each worksheet contains a table formatted the exact same way - the only difference is the worksheet names.

Here is what I have so far:

Sub CommandButton2_Click()

Sheets(Array("Sheet1", "Sheet2")).Select
Sheets("Sheet2").Activate
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow

Dim myValue As Variant

myValue = InputBox("Enter Thought Leadership Title", "New Thought    Leadership", "XXXXX")

Sheets(Array("Sheet1", "Sheet2”)).Select
Range("F5").Value = myValue

End Sub

This has gotten me exactly what I want for 'Sheet1' - adds a column (F) and inputs a header name specified through the dialog box into cell F5 while copying the formatting from the column immediately to the right. However, all it does for Sheet2 (and all others, but removed them for brevity) is add in a column without copying formatting from the right or adding the text specified through the dialog box.

Beginner VBA "programmer" and have gotten this far through using the forums here.

Hopefully I was clear - thanks for any input.

回答1:

Just loop through Array("Sheet1", "Sheet2"). Adding the Type:=2 parameter to the InputBox will ensure that myValue will always be a string. If the user cancels the InputBox then myValue = "".

Sub CommandButton2_Click()
    Dim ws
    Dim myValue As Variant

    myValue = Application.InputBox(Prompt:="Enter Thought Leadership Title", Title:="New Thought    Leadership", Default:="XXXXX", Type:=2)

    If myValue <> "" Then
        For Each ws In Array("Sheet1", "Sheet2")
            With ws
                .Columns("F:F").Insert Shift:=xlToRight
                .Range("F5").Value = myValue
            End With
        Next
    End If

End Sub

回答2:

An alternative to @ThomasInzina is this:

Sub commandButton2_Click_Test()
Dim myValue As Variant

For Each Worksheet In ActiveWorkbook.Worksheets
    With Worksheet
        .Range("F:F").EntireColumn.Insert shift:=xlToRight, copyOrigin:=xlFormatFromRightOrBelow
        myValue = InputBox("Enter Thought Leadership Title", "New Thought    Leadership", "XXXXX")

    End With
Next Worksheet
Worksheets("Sheet1").Range("F5").Value = myValue ' I'm not sure where you wanted this,
Worksheets("Sheet2").Range("F5").Value = myvalue ' or what it does, so tweak as necessary.
End Sub

I tried to keep it as similar to your code as possible, while avoiding .Select (as mentioned in my comment below OP).

Note: This will loop through all worksheets in your workbook. You can add a line If worksheet.name = "Sheet1" or worksheet.name = "Sheet2" to only run it on those.