Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Prompt
Hi I have the following code which is working well, but I'd like to improve
on it. Ideally I would like a msg prompt to select "AK2" Perhaps I could name the range AK2 as I have "RANKING"? Not sure about msg prompt? If anyone could help, I would appreciate it. Kind Regards Tanya Private Sub CommandButton3_Click() UnProtect_Workbook Application.Goto Reference:="RANKING" ActiveWindow.SmallScroll Down:=-9 'Sort by Sem-2 Rank Selection.Sort Key1:=Range("AK2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveWindow.SmallScroll ToRight:=2 Protect_Workbook End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Prompt
Hi Tanya
I'm not sure what you are asking to do but the code below will cover the two main options for msg's in VBA, the first one uses an inputbox that will ask the user to enter a something which you define in teh prompt of the msg, this is good as it allows you to prompt the user for different values meaning your code can be used to do the same thing but in different areas of the sheet. the second is just a simple msgbox that will prompt the user to do something like select the range AK2, other than these options you could create your own userform with what ever you need on it and then call that form when yuo need it. Option Explicit Dim MyRef As String Private Sub CommandButton3_Click() UnProtect_Workbook MyRef = InputBox("Enter a either Ranking or something else" & vbNewLine & _ "That you wish to sort by", "Name Me what you wish", "Ranking") Application.Goto Reference:=MyRef ActiveWindow.SmallScroll Down:=-9 'Sort by Sem-2 Rank Selection.Sort Key1:=Range("AK2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveWindow.SmallScroll ToRight:=2 Protect_Workbook End Sub Private Sub CommandButton3_Click() UnProtect_Workbook MsgBox ("Please select the cell AK2") ActiveWindow.SmallScroll Down:=-9 'Sort by Sem-2 Rank Selection.Sort Key1:=Range("AK2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveWindow.SmallScroll ToRight:=2 Protect_Workbook End Sub I hope this is of some help to you Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
save prompt for user exit, but no save prompt for batch import? | Excel Discussion (Misc queries) | |||
Help on Prompt | Excel Worksheet Functions | |||
Prompt for Value | Excel Programming | |||
Prompt for Value | Excel Programming | |||
Prompt for a range to apply code to | Excel Programming |