Pop up box to find a number in a table

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,026
Could some one tell me what code you would use to have a box pop up to ask a question " What number are you looking for ?" and when they type the number in it takes them to the number in a table. I want to be able to press a button to have this question box pop up.

Thanks for you help
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Here's a quick little way you can do that.
It assumes your table is C1:F20. (Change that in the code to suit.)
It also assumes your table is on the active sheet when you run this.
The code just goes into a standard module.
Code:
Sub FindMyVal()
Dim x As Range
MyVal = Application.InputBox("What number are you looking for?", "Enter Search Value")
If Len(MyVal) = 0 Or MyVal = False Then Exit Sub
Set x = Range("C1:F20").Find(MyVal, lookat:=xlContents)
If x Is Nothing Then
    MsgBox "Requested value not found.", vbInformation, "Search Results"
  Else
    x.Select
End If
End Sub
Hope it helps.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Code:
Sub test()
Dim r As Range, res As Double
res = Application.InputBox("What number?", Type:=1)
With Sheets("sheet1")
    Set r = .Cells.Find(res, , , xlWhole)
    If Not r Is Nothing Then
        Application.Goto r
    End If
End With
End Sub
 

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,026
Works great now a really dumb question, I have been using office 97 forever and just got 2003. I inserted a command button and am trying to attack the code you gave me to it by clicking on the button to asign macro but I no longer have that option.

Thanks for you help
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Did you create commandbutton from the control tool box?
 

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,026
Yes, usally I can right click on it and the asign macro but I no longer have that option
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

When you right click on the commandbutton, didn't you have
View Code?
 

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,026
I did, but it just took me to the code, I went to look after reading you question and created another button and it asked me to asign macro this this time. Don't know what I did different but it worked. Thank you all for your help : )
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
jcaptchaos2 said:
I did, but it just took me to the code, I went to look after reading you question and created another button and it asked me to asign macro this this time. Don't know what I did different but it worked. Thank you all for your help : )
Hello,
What you describe is not a difference between '97 and '03.
I believe what you did different was to create your button (this last time) from the Forms toolbar. This is known simply as a "Button". You can right click on it and get the dropdown menu that includes "Assign macro" and this code is stored in a standard module.
I suspect the first button you created was from the Controls Toolbox toolbar, which means it was actually a "CommandButton". This is also known as an activeX button because it's in fact an activeX control whereas the Forms button is not. When you right click on one of these you get almost the same dropdown menu but instead of Assign macro, it offers View code instead. The code for an activeX control gets stored in the sheet code module of whatever sheet your control is planted on.

Both of these controls are available in XL '97 and '03.

Does that help clear it up any?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,169
Messages
5,570,656
Members
412,335
Latest member
cinciri99
Top