Command Button Search

[peerogel]

Hello, I have found great information in this forums but now im stuck.

Im trying to make the command button search within the workbook for the value of cell H2.

This is what i have right now, but instead of the input box i want it to automatically choose the value in cell H2 and search within the workbook when i click the command button.

I am basically trying to create a 'menu' in sheet 1. after selecting the what i want in from the scroll bar i want to click the command button so it will take me to the appropriate sheet. If there is an easier way to do this please let me know.

Hope this is enough info.


Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim rFound As Range
Dim strName As String

On Error Resume Next
strName = InputBox("GO!!!!!!!")
If strName = "" Then Exit Sub
For Each ws In Worksheets
With ws.UsedRange
Set rFound = .Find(What:=strName, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)
If Not rFound Is Nothing Then
Application.Goto rFound, True
Exit Sub
End If
End With
Next ws
On Error GoTo 0

MsgBox "Value not found"
End Sub

Private Sub ScrollBar1_Change()
End Sub

Thanks for the help.

 

[jaslake]

Hi peerogel

I don't pretend to know what you're trying to do. However, assuming your Command Button works otherwise, to do this

make the command button search within the workbook for the value of cell H2

change this line of code from

strName = InputBox("GO!!!!!!!")

to

strName = Range("H2").Value

What IS the appropriate sheet...is there a particular sheet and sheet range you wish to search?

i want to click the command button so it will take me to the appropriate sheet

John

 

[peerogel]

Thanks for the quick responce, it worked out great.

What im trying to do is to create a menu of different speed calculation formulas in Sheet 1. I created the menu of the different formulas and their description with a scroll bar.

For example in the scroll menu, Cell F1 names the formula like "radius", Cell G2 describes the formula and in Cell H2 i just typed for ex. 'aa'

In Sheet 2, cell A1 i typed 'aa', then created the formula using a few cells to find the radius of a road corner.

So in the menu, once i select the formula i want, i click the command button and it will take me to sheet 2 where the formula is located. And then do the same with different sheets.

So far the way i have it set up, thanks to your help, it works fine but i wasnt sure if there was an eisier way to do that.


One more question, if possible, I wanted to protect the cells so they didnt modify the formulas so i created an input box. I got it to work but if i protect the sheet it will come back with an error. Is there a way around this or is it not possible.

I get the following error:

Run-time error '1004':
Application-defined or object-defined error.

Thanks for the help.

 

[jaslake]

If Sheet Protection is indeed the issue (that is, your code works on the Unprotected Sheet) then, with code, Unprotect the Sheet, do your thing, then, with code, Protect the Sheet.

I don't know if this has any application to your issue but consider looking at

shts.Protect contents:=True, userinterfaceonly:=True

John

 

[peerogel]

Thanks for the reply but I am not sure how to use that, but i think i wont need it after all. Thanks I really appreciate your help. This excel forum has been really helpful.

 

[peerogel]

Since my issue was resolved i was wondering if i had to close the thread (some how). I believe i have seen threads where people get asked to close them. If i do, how do i do that? Thanks for the help.