Using a user input string as part of the name of a range 1

[jlt199]

Hi

I'm building a macro where I want to use part of a string input by a user as the name of a named range. The user can put anything in the string - the strings are used as the column headers of the output of the macro.

As part of the macro I need to define a number of named ranges. I would like to use the strings entered by the user in the names - rather than generic numbering. I've manged to remove all the spaces from these strings using

varStr = Replace(userStr, " ", "")

But the user may use illegal naming characters in their string, an example of a string that is causing problems is:

Ref (ppm.m)

I would like to be able to just use all of the characters in the string before any illegal naming characters. So in the example above I would use the string "REF".

Is anyone able to advise me of an efficient method of achieving this? I'm developing the macro to work in Excel 2003.

Many thanks

 

[SkipVought]

Hi,

You ought to text toi see how Excel handles such values, using Insert > Name > Create -- Create Names in TOP row
But in general, I'd use the Select Case statement to include only a-z, 0-9...

Select Case
   case "a" to "z", "A" to "Z", "0" to "9"
     'keep these characters
   case " "
     'do a replacement
   Case else
     'IGNORE these characters
end select

BTW, you ALSO must exclude valid cell references like A1 and PRECEED any name that begins with a digit, with an UNDERSCORE, like _209B

Alternatively, put the user entered value in a cell and use the Insert > Name > Create -- Create Names in TOP row
method to harvest the name that Excel would make from an entry. THAT is the safest approch.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jlt199]

I'm not worried about the first character being legal, I'm adding a prefix to the user entered string to form the name.

I hadn't considered the cell reference issue though... Is it possible to code something such as if illegal name remove last character, then keep looping until name is legal? I haven't figured out error handling yet, still muddling through trying to achieve my goals only using the basics I know. Not the most efficient approach I know.

I think you have misunderstood what I was trying to achieve with the illegal caharacters, or I have misunderstood your suggestion. If there are illegal characters then I want to use only the portion of the string to the left of the first illegal character. I think you suggestion will just remove the illegal characters, so in the example string in my first post you would land up with

"Refppmm"

and not

"Ref"

which is what I'm looking for. I do appreciate your time though.

On a side note, what tags do you use to wrap around your code in order to get the code box?

Many thanks

 

[SkipVought]

This would do it, yes?

Select Case
   case "a" to "z", "A" to "Z", "0" to "9"
     'keep these characters
   case " "
     'do a replacement
   Case else
     'exit the for...next loop
     Exit For
end select

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jlt199]

varStr = Trim(varStr)
    For j = 1 To Len(varStr)
        Select Case Mid(varStr, j, 1)
            Case "a" To "z", "A" To "Z", "0" To "9"
            Case Else
                Exit For
        End Select
    Next j
If j < Len(varStr) Then varStr = Left(varStr, j - 1)

Thanks

 

[jlt199]

Sorry, should be

varStr = Replace(varStr, " ", "")
For j = 1 To Len(varStr)
    Select Case Mid(varStr, j, 1)
        Case "a" To "z", "A" To "Z", "0" To "9"
        Case Else
            Exit For
        End Select
    Next j
If j < Len(varStr) Then varStr = Left(varStr, j - 1)

 

[PHV]

I'd replace this:
If j < Len(varStr) Then
with this:
If j <[!]=[/!] Len(varStr) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jlt199]

I actually noticed that this code would not pick up the case where the illegal character is the last character in the string.

I have therefore moved

varStr = Left(varStr, j - 1)

into the 'Case Else' statement above 'Exit For' and removed that If-statement altogether.