PDA

View Full Version : Solved: Use VBA to create a named range



tyrese215
04-25-2012, 10:35 PM
I need some VBA code where the user can select a range using their mouse or keyboard, then

they click on a button and your vba code will create a named range, using the name to the left of the top left of the active range selected.

Please implement this on the workbook attached:


Basically if the user select the range b1:L7 and then clicks the button "Make New NamedRange"

Then the named range that should be created should be "aaa"

We would like some error handling built in so if a named range cannot be created because

there is an invalid character to the left cell of the top left of the active selected range.

So say if aaa was actually "a s cc$@4 4"

Then a message box should appear and say.

Sorry, invalid characters in the desired named range please recheck.


Also, if this named range exists, then the code should say:

Sorry, name range already exists. Please recheck

Lastly, if name range is successfully created we need a message box to appear to read:

Congratulation, named range "xxx" is successfully created

where xxx is the named range name that is created.


Please implement your solution to the attached workbook..

Thank you

Bob Phillips
04-25-2012, 11:58 PM
Is this a homework project?

tyrese215
04-26-2012, 12:00 AM
Hi there,

No this is not a home work project. I am an analyst and my manager asked me how we can do this. I researched everywhere and couldnt find an answer..

Kindly assist if you can

Jan Karel Pieterse
04-26-2012, 12:17 AM
Like so:

Sub CreateName()
Dim oRng As Range
Dim sName As String
Dim oNm As Name
On Error Resume Next
Set oRng = Application.InputBox("Please select the range to be named", "Create a new range name", , , , , , 8)
If Not oRng Is Nothing Then
sName = oRng.Cells(1, 1).Offset(0, -1).Value
Set oNm = ActiveWorkbook.Names(sName)
If Not oNm Is Nothing Then
MsgBox "Range name already exists!", vbExclamation + vbOKOnly
Else
oRng.Name = sName
Set oNm = ActiveWorkbook.Names(sName)
If oNm Is Nothing Then
MsgBox "Range name contains illegal characters!", vbExclamation + vbOKOnly
Else
MsgBox "Range name '" & sName & "' created", vbOKOnly + vbInformation
End If
End If
End If
End Sub

tyrese215
04-26-2012, 04:57 PM
Thank you soooo much. Works like a charm.. :)