EXCEL VBA: Problem with error handling in inputbox

Question

4.00/5 (1 vote)

See more:

Hello MVP's and Experts!

I need help in error handling for Excels' inputbox. I just want to prompt the user that he/she is not inputting proper range/cell location. Also, if the inputbox is blank and they press OK, should prompt the user to enter cell location or a cell. Many Thanks...

This is my code:

VB

Sub Find_Duplicates_Inte()
Dim i As Integer
Dim c As Object

i = 35

On Error Resume Next

Set c = Application.InputBox(prompt:="Select starting cell", _
              Title:="Chrispogs", Type:=8)
On Error GoTo 0

     If c Is Nothing Then Exit Sub

c.Select

Application.ScreenUpdating = False

While ActiveCell <> ""
    If ActiveCell = ActiveCell.Offset(-1, 0) Then
        ActiveCell.Interior.ColorIndex = i
        ActiveCell.Offset(-1, 0).Interior.ColorIndex = i
        If ActiveCell = ActiveCell.Offset(1, 0) Then
        Else
            If i = 36 Then
                i = 35
            Else
                i = i + 1
            End If
        End If
        ActiveCell.Offset(1, 0).Select
    Else
        ActiveCell.Offset(1, 0).Select
    End If
Wend

Application.ScreenUpdating = True

End Sub

Posted 5-Apr-11 23:29pm

arbilcc

Updated 6-Apr-11 1:02am

Slacker007

v3

Add a Solution

Comments

arbilcc 7-Apr-11 1:12am

I really appreciate your advise, and find it very useful for a newbie like me. Decided to delete the subroutine line and change the resume next to an error handler to prompt that the process was cancelled when a user cancels the inputbox. I also found out that excel automatically validate and prompt if a range inputted is not valid. But is there a way to change the automatic validation message to my own message? Thank you for your help.

Slacker007 7-Apr-11 6:27am

Your welcome. :)

Unfortunately I don't think you can stop the automatic messages from Microsoft products. I'm sure there might be a way but I have yet to find it. Most of these "auto" messages don't have error numbers attached to them so you can't trap them...very frustrating to say the least.

Let me know if you need any more help with this or other VBA problems.

Slacker007 7-Apr-11 6:28am

A reminder: Dalek Dave is very good with Excel and VBA for Excel. He would be a great resource.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Slacker007 · Accepted Answer · 2011-04-06T01:00:00

In your line:

If c Is Nothing Then Exit Sub

You are exiting the sub routine without handling the blank variable for the input box. Write and "If" statement that tests the contents of the variable "c" and handle the test accordingly.

I don't like to use the following line unless I absolutely have to when writing VBA code:

On Error Resume Next

When you use this line then you are saying that if I get an error still run the next line of code and forget about the error. This is not good in most situations. Instead, write an error handler routine to handle the error.

Please note: Good VBA programming practices can be found all over the internet especially on MSDN (I know, hard to believe).

You will not find a lot of VBA stuff here at CP because this site "mainly" deals with C++, C#, and VB.Net coding. However, myself, Dalek Dave, and others have extensive VBA backgrounds and would be more than glad to help.

Dalek Dave, is a good Excel VBA guru and our resident accountant. His profile is here[^]

If you have any more questions then don't hesitate to ask.

-- Good luck.