Select just one cell using input box

Dear Experts:

I would like to run the following macro

An inputbox should prompt the user to ...

... just select (1) one cell
... the selected cell is to conform to the following cell format '##-###-##-##', the pound sign standing for numbers, e.g. 55-443-22-99
... if one or both of the requirements is false the macro is to say so and to exit
... if both requirements are true the macro is to select the 'second' cell in that column. For example: the user selects 'C7', the macro is to move (offset) this selection to 'C2'

Help is much appreciated. Thank you very much in advance for your valuable help.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
In my first code, I supposed the number you had was for example 123456789 then formatted as 12-345-67-89 but if you have directly entered 12-345-67-89 then

pls try this

Sub Macro()

On Error Resume Next
Set myRange = Application.InputBox("Please select the cell :", Default:=Range("A1").Address(0, 0), Type:=8)
On Error GoTo 0

If myRange Is Nothing Then
    MsgBox "You have selected no cell"
    Exit Sub
End If

If myRange.Count > 1 Then
    MsgBox "You have selected more than one cell"
    Exit Sub
ElseIf myRange.DisplayFormat.NumberFormat <> "##-###-##-##" _
        And Not (myRange.Value Like "##-###-##-##") Then
    MsgBox "This cell has not a valid format"
    Exit Sub
End If
Cells(2, myRange.Column).Select
End Sub

Open in new window

Regards
0
 
Rgonzo1971Commented:
Hi,

pls try

Sub Macro()

On Error Resume Next
Set MyRange = Application.InputBox("Please select the cell :", Default:=Range("A1").Address(0, 0), Type:=8)
On Error GoTo 0

If MyRange Is Nothing Then
    MsgBox "You have selected no cell"
    Exit Sub
End If

If MyRange.Count > 1 Then
    MsgBox "You have selected more than one cell"
    Exit Sub
ElseIf MyRange.DisplayFormat.NumberFormat <> "##-###-##-##" Then
    MsgBox "This cell has not a valid format"
    Exit Sub
End If
Cells(2, MyRange.Column).Select
End Sub

Open in new window

Regards
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Rgonzo,
thank you very much for your swift and professional help.

Thank you very much for your great code. It works just fine although line 15 of the code only works properly if the cell that is selected has been formatted as follows before running the macro:
'user-defined' ##-###-##-##

If the cell format is 'just' standard and the number that is selected is e.g. 55-554-33-99 the macro will come up with the msgbox on line 16. For me this does not make sense since you correctly (in my view) used the 'DisplayFormat'.

Any idea why this is so?

Regards, Andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
Great job, as always, Rgonzo. Thank you very much for your professional support. I really appreciate it.

Regards, Andreas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.