Range Prompt

[Tanya]

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

 

[Incidental]

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