Define A Single Cell using VBA-versus right click define name

[Revnou]

My limit of writing VBA is record various macros and add it together. Making super elementary changes. Superrrrrr noob. I can't understand Macro for the life of me. I spent a week scouring all over the internet and attempt to tweak whatever code is out there, but failed miserably.

I normally right click and select define name on the excel file randomly in various parts of the workbook. Is there a vba code for this? I hope to imbed it with my existing macro. Any help will be appreciated.

Super thank you in advance.

 

[DanteAmor]

Hi @Revnou. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

If you want to open the dialog box run this macro:

Sub CreateName_1()
  Application.Dialogs(xlDialogDefineName).Show "example1", Selection
End Sub

If you want to define a range name:

Sub CreateName_2()
  ActiveWorkbook.Names.Add Name:="example2", RefersTo:=Selection
End Sub

Or this:

Sub CreateName_3()
  ActiveWorkbook.Names.Add Name:="example3", RefersTo:="Sheet1!$A$10:$B$11"
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[Revnou]

I must not understand something super basic. When I use the code, it keeps showing "example2" . It doesn't change/define to the word in the cell. For example, I am clicking on a random cell that says 123cat,when i hit the macro, it defines "example2" instead of "123cat". How do I fix that so it defines to the text string in the cell? The dialog box will disrupt macro flow as I hope to use this peace to combine with other macros. The third macro doesn't work. So I tried the macro below.

I tried it with this macro:

Sub CreateName_2()
ActiveWorkbook.Names.Add Name:="example2", RefersTo:=Selection
End Sub

 

[Revnou]

Hi @Revnou. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

If you want to open the dialog box run this macro:

Sub CreateName_1()
  Application.Dialogs(xlDialogDefineName).Show "example1", Selection
End Sub

If you want to define a range name:

Sub CreateName_2()
  ActiveWorkbook.Names.Add Name:="example2", RefersTo:=Selection
End Sub

Or this:

Sub CreateName_3()
  ActiveWorkbook.Names.Add Name:="example3", RefersTo:="Sheet1!$A$10:$B$11"
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

Whoohoo. Thank you DanteAmor. I was able to revised the macro and the below works.

Dim myCell As Range
Set myCell = ActiveCell

ActiveWorkbook.Names.Add Name:=ActiveCell, RefersTo:=Selection
End Sub

 

[DanteAmor]

I'm glad to hear that

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

Example:

Sub Test()
  Dim myCell As Range
  Set myCell = ActiveCell
  ActiveWorkbook.Names.Add Name:=ActiveCell, RefersTo:=Selection
End Sub

 

[Revnou]

Whoohoo. Thank you DanteAmor. I was able to revised the macro and the below works.

Dim myCell As Range
Set myCell = ActiveCell

ActiveWorkbook.Names.Add Name:=ActiveCell, RefersTo:=Selection
End Sub

 

[Revnou]

Providing full Macro incase someone in the future needs it.

Sub DefineName()
Dim myCell As Range
Set myCell = ActiveCell

ActiveWorkbook.Names.Add Name:=ActiveCell, RefersTo:=Selection
End Sub

 

[jkpieterse]

That code can be shortened to:

Sub DefineName()
    ActiveCell.Name = ActiveCell.Value
End Sub

 

[Revnou]

That code can be shortened to:

Sub DefineName()
    ActiveCell.Name = ActiveCell.Value
End Sub

Thank you soo much.