Rename cell via textbox

[richertt]

Hello Excell Guru's,

I want to make a text input box that asks for the columns of MFG, PN, Description, Quantity and reference designator, and then renames the headers accordingly. I am then selecting those particular columns and doing other things with them. I'm having to rename the columns manually at the moment and thought having an input box would be very cool. Thanks in advance!

ActiveSheet.Select
MFG = WorksheetFunction.Match("MFG", Rows("1:1"), 0)
PN = WorksheetFunction.Match("PN", Rows("1:1"), 0)
DESC = WorksheetFunction.Match("DESC", Rows("1:1"), 0)
QTY = WorksheetFunction.Match("QTY", Rows("1:1"), 0)
POS = WorksheetFunction.Match("REF", Rows("1:1"), 0)


ActiveSheet.Columns(MFG).Copy Destination:=Sheets("MIKE_BOM").Range("A1")
ActiveSheet.Columns(PN).Copy Destination:=Sheets("MIKE_BOM").Range("B1")
ActiveSheet.Columns(DESC).Copy Destination:=Sheets("MIKE_BOM").Range("C1")
ActiveSheet.Columns(QTY).Copy Destination:=Sheets("MIKE_BOM").Range("D1")
ActiveSheet.Columns(POS).Copy Destination:=Sheets("MIKE_BOM").Range("E1")

 

[mikerickson]

What do you mean "rename the columns"? The posted code shows no names.

 

[richertt]

Sorry, yes

 

[Fluff]

How about

Sub RenameHeader()
   Dim Cols, ary
   Dim i As Long
   
   ary = Array("MFG", "PN", "DESC", "QTY", "REF")
   Cols = InputBox("Please enter column numbers like 1,3,5")
   Cols = Split(Cols, ",")
   If UBound(Cols) <> 4 Then MsgBox "you need to enter 5 column numbers": Exit Sub
   For i = 1 To UBound(Cols)
      Cells(1, val(Cols(i))).Value = ary(i)
   Next i
End Sub

 

[richertt]

Let me try this again,

I have a multiple Bill of materials spreadsheets, which are different from spreadsheet to spreadsheet. I currently rename the columns I want to extract, and then I have code that copies these columns to another spreadsheet and formats, etc. Instead of manually changing the column names and running my code, I would like to have a text input box that asks which column is the "manufacturer", "PN", "Description", "Quantity" and "Reference Designators". Once the inputs are made, rename the columns and continue on with the rest of my code.

 

[mikerickson]

What do you mean "rename a column"?

Do you mean assign that column to a Named Range? Or do you mean change the value in the header cell at the top of the column? Or do you mean something else?

 

[richertt]

What do you mean "rename a column"?

Do you mean assign that column to a Named Range? Or do you mean change the value in the header cell at the top of the column? Or do you mean something else?

Value of the header cell at the top of column is what I'm talking about.

 

[mikerickson]

Perhaps

Dim uiCell As Range
Dim oneHeader As Variant

For Each oneHeader In Array("manufacturer", "PN", "Description", "Quantity", "Reference Designators")
    Set uiCell = Nothing

    On Error Resume Next
    Set uiCell = Application.InputBox("Click on the " & oneHeader & " column.", Type:=8)
    On Error GoTo 0

    If uiCell Is Nothing Then Exit For: Rem cancel pressed

    uiCell.EntireColumn.Cells(1, 1).Value = oneHeader
Next oneHeader

 

[richertt]

Perhaps

Dim uiCell As Range
Dim oneHeader As Variant

For Each oneHeader In Array("manufacturer", "PN", "Description", "Quantity", "Reference Designators")
    Set uiCell = Nothing

    On Error Resume Next
    Set uiCell = Application.InputBox("Click on the " & oneHeader & " column.", Type:=8)
    On Error GoTo 0

    If uiCell Is Nothing Then Exit For: Rem cancel pressed

    uiCell.EntireColumn.Cells(1, 1).Value = oneHeader
Next oneHeader

That worked perfectly. Thank you!