Format Problem with Input Box

will2learn

Board Regular
Joined
Dec 1, 2005
Messages
144
I'm experiencing a problem when a number is typed into the input box. Is there anyway to force the entry to be read as text?

Code:
Private Sub OptionButton4_Click()
    Dim Na
    Dim Rng As Range
    Dim F As Range
    Dim FirstAddress As String
    Dim R As Long
    Dim LastRow As Long
    Na = InputBox("Job Number")
    
    If Na = "" Then Exit Sub
    
    Set Rng = Range("DutyID")
    Set F = Rng.Find(What:=Na, After:=Rng.Cells(Rng.Rows.Count, Rng.Columns.Count))
    If Not F Is Nothing Then
    
    Sheets("Data").Select
    LastRow = Range("A65536").End(xlUp).Row
    Range("A1:AF1").Select
    Selection.AutoFilter
    Application.CutCopyMode = False
    Range("A2:AF" & LastRow).Select
    Selection.AutoFilter Field:=6, Criteria1:="=" & Na, Operator:=xlAnd
    Selection.ClearContents
    Selection.AutoFilter
    Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Worksheets("Data").AutoFilterMode = False
    
    Sheets("Data").Select
    LastRow = Range("A65536").End(xlUp).Row
    Range("BX1:BZ1").Select
    Selection.AutoFilter
    Application.CutCopyMode = False
    Range("BX2:BX" & LastRow).Select
    Selection.AutoFilter Field:=1, Criteria1:="=" & Na, Operator:=xlAnd
    Selection.ClearContents
    Selection.AutoFilter
    Selection.Sort Key1:=Range("BX2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Worksheets("Data").AutoFilterMode = False
    
    Sheets("Data").Select
    LastRow = Range("A65536").End(xlUp).Row
    Range("CB1:CF1").Select
    Selection.AutoFilter
    Application.CutCopyMode = False
    Range("CB2:CF" & LastRow).Select
    Selection.AutoFilter Field:=2, Criteria1:="=" & Na, Operator:=xlAnd
    Selection.ClearContents
    Selection.AutoFilter
    Selection.Sort Key1:=Range("CC2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Worksheets("Data").AutoFilterMode = False
    Sheets("StartScreen").Select
    
    Else
        MsgBox ("Not found")
    End If

End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
All entries in input boxes, text boxes etc. is text.
I think this change will solve your problem :-

Code:
Dim Na As String


You could equally use
Dim Na As Integer
when Excel helpfully does the conversion.
 

will2learn

Board Regular
Joined
Dec 1, 2005
Messages
144
Hi, BrianB

Had already tried your suggestion but did not work. I presume its a format issue but am stuck.
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
You have not explicitly said so, but I presume that your problem is that your code is not finding values that you know are there ?

I did a quick test of your Input/Find code - it works Ok.

One thing to try is a manual Edit/find - perhaps recording the code and transferring that.

As you suggest, it is necessary to have Find value and worksheet data in the same format, although my test data was 'General'.

This is a possible fault. You have not included the worksheet name :-

Set Rng = Range("DutyID")
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,057
Messages
5,835,162
Members
430,343
Latest member
t0m_c

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