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

[campbells]

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.

 

[acw]

Campbell

How about

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]

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]

Campbell

how about

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

 

[campbells]

Tidy.

Appreciated,
Campbell.