Pop up box to find a number in a table

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,032
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Yes, usally I can right click on it and the asign macro but I no longer have that option
 
Upvote 0
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 : )
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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
Back
Top