Store the results of a formula as a variable

julesl

New Member
Joined
Sep 21, 2004
Messages
32
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 :biggrin:
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

this works for me
Code:
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

New Member
Joined
Sep 21, 2004
Messages
32
Brilliant, that works perfectly.

Thanks very much for your help

J
 

Forum statistics

Threads
1,136,308
Messages
5,674,990
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top