Store the results of a formula as a variable

[julesl]

Hi

I have a form where the user selects a name from a drop down list (Name from NameList) which then copies all the records for that person to a separate sheet.

What I want to do is rename the new worksheet using a shortened version of the Name.

I need some code which looks something like this but I'm not sure of the correct syntax:

Dim Shortname
Dim Name

Name = cmbName.Value (Name = the value from the drop down menu e.g Aaaaaaaaaaaaaaaaaa)

Shortagent = VLOOKUP(Name,NameList,2,FALSE)

Sheets.Add.Name = "Report_" & Shortname

-----------------------------------

NameList (which is stored on a worksheet within the spreadsheet)

Name...............................Short Name

Aaaaaaaaaaaaaaaaaa...............A
Bbbbbbbbbbbbbbbbbb...............B
Cccccccccccccccccc...................C
Dddddddddddddddddd...............D
Eeeeeeeeeeeeeeeeee................E

So if the user selects Aaaaaaaaaaaaaaaaaa from the form's drop down menu, the worksheet is renamed Report_A

Can anyone help me please

Thanks

J

 

[erik.van.geit]

Hi,

this works for me

Private Sub CommandButton1_Click()
Dim Shortname
Dim LongName

LongName = cmbName.Value
    If LongName = "" Then
    MsgBox "Please select name", 48, "ERROR"
    Exit Sub
    End If

Shortname = Application.WorksheetFunction.VLookup(LongName, Range("NameList"), 2, False)

Sheets.Add.Name = "Report_" & Shortname
End Sub

avoid using terms like Object, Areas, Str, Filter, Title, Name, Item, Time, since they are used (needed) by VBA itself: that's why the first character changes in an uppercase automatically!

test this:
type: name = "excel"
pressing ENTER it will change in Name = "excel" (see the uppercase!)

you can do the same with item, ...

you see ?

From VBA Help on Visual Basic Naming Rules:

"Generally, you shouldn't use any names that are the same as the functions, statements, and methods in Visual Basic. You end up shadowing the same keywords in the language. To use an intrinsic language function, statement, or method that conflicts with an assigned name, you must explicitly identify it. Precede the intrinsic function, statement, or method name with the name of the associated type library. For example, if you have a variable called Left, you can only invoke the Left function using VBA.Left."

kind regards,
Erik

 

[julesl]

Brilliant, that works perfectly.

Thanks very much for your help

J