macro to define name for a cell based of it's content

campbells

New Member
Joined
Sep 29, 2006
Messages
10
Hello

I was hoping i might have some coding help with a macro that would:

1. read the content of the current cell (always text)
2. display a message/confirm box with that content, with a string appended to the end of it, and upon confirm of this -
3. define a name for the cell according to this string.
4. move the selection one to the right

so, if you had a cell with content "fooBar23", i would be great to have a macro that would read the content of the cell, display it to me in a confirm dialogue that would be 'define name for cell as "fooBar_extra"?' ...and upn confirm, define the cell name as that string (or on denial, exit w/o naming). selction would then move to the rigth so the macro could be fired off again

it would be great if this would be functional for a selected range too, not just a single cell, where the user is sure the outcome will be fine and wouldnt need the confirm dialogue for each and every cell.

basically, i have a whole lot of cell naming to do based off the content of those cells, but some of the cell's contents will make for poor names and so need to be handled differently from the rest.

Many Thanks,
Campbell.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Campbell

How about

Code:
Sub ccc()
  If Selection.Cells.Count = 1 Then
    If MsgBox("Define name for cell as: " & ActiveCell.Value & "_extra", vbOKCancel) = vbOK Then
      ActiveWorkbook.Names.Add Name:=ActiveCell.Value & "_extra", RefersTo:="='" & ActiveSheet.Name & "'!" & ActiveCell.Address
    End If
    ActiveCell.Offset(0, 1).Select
  Else
    For Each ce In Selection
      ActiveWorkbook.Names.Add Name:=ce.Value & "_extra", RefersTo:="='" & ActiveSheet.Name & "'!" & ce.Address
    Next ce
  End If
  
End Sub


Tony
 

campbells

New Member
Joined
Sep 29, 2006
Messages
10
Tony,

Does the trick nicely! Thankyou.

Is it possible to have an editable text field in the confirmation box? Such that if you make edits to the 'suggested' name, it will take those and use it for the name definition.


Much Obliged,
Campbell.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Campbell

how about

Code:
Sub ccc()
  If Selection.Cells.Count = 1 Then
    Resp = MsgBox("Define name for cell as: " & ActiveCell.Value & "_extra", vbYesNoCancel)
    If Resp = vbYes Then
      ActiveWorkbook.Names.Add Name:=ActiveCell.Value & "_extra", RefersTo:="='" & ActiveSheet.Name & "'!" & ActiveCell.Address
    ElseIf Resp = vbNo Then
      ActiveWorkbook.Names.Add Name:=InputBox("Enter the name you want to use"), RefersTo:="='" & ActiveSheet.Name & "'!" & ActiveCell.Address
    End If
    ActiveCell.Offset(0, 1).Select
  Else
    For Each ce In Selection
      ActiveWorkbook.Names.Add Name:=ce.Value & "_extra", RefersTo:="='" & ActiveSheet.Name & "'!" & ce.Address
    Next ce
  End If
  
End Sub

Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,113,795
Messages
5,544,337
Members
410,603
Latest member
rseckler
Top