Getting value from a TextBox to a vbs script???

  • Thread starter Thread starter kandinsky
  • Start date Start date
K

kandinsky

Hi. I am using this piece of code - kindly supplied by DaveP - to search
for cells containg certain names:

:::::START:::::::::::::::::::::::::::::::
Option Explicit
Sub SearchForName()

Dim FoundCell As Range
Dim CellWithName As Range


With Worksheets("sheet1")
Set CellWithName = .Range("A1")
If Trim(CellWithName.Value) = "" Then
MsgBox "Please type something"
Exit Sub
End If

With .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set FoundCell = .Cells.Find(What:=CellWithName.Value, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "not there"
Else
Application.Goto reference:=FoundCell, Scroll:=True
End If
End With

End Sub

:::::END::::::::::::::::::::::::::::::

Can i use a textbox in my worksheet as the datainput instead? (And
how). If i fx. make a TextBox and call it sString, how do I get the
value from whatevers in it, into the script above?

Cheers...
 
I assume you mean a worksheet textbox off of the control toolbox.

Replace this

With Worksheets("sheet1")
Set CellWithName = .Range("A1")
If Trim(CellWithName.Value) = "" Then
MsgBox "Please type something"
Exit Sub
End If


with this

With Worksheets("sheet1")
Set CellWithName = .Textbox.Text
If Trim(CellWithName.Value) = "" Then
MsgBox "Please type something"
Exit Sub
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I do mean that yes. But when I try the .TextBox.Text as you suggest
then get the following:

"Runtime error

Object required"

And it's highlighting the line:

Set CellWithName = .TextBox1.Text


Obviously i'm not a programmer, he he
 
Sorry,

I left some of the code that assumed a range in there. Try this lot instead

Sub SearchForName()
Dim FoundCell As Range
Dim CellWithName as String

With Worksheets("sheet1")
CellWithName = .TextBox1.Text
If Trim(CellWithName) = "" Then
MsgBox "Please type something"
Exit Sub
End If

With .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set FoundCell = .Cells.Find(What:=CellWithName, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "not there"
Else
Application.Goto reference:=FoundCell, Scroll:=True
End If
End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Absolutely b-e-a utiful!

Thanx a lot mate. Just make the whole feel of the worksheet that mor
intuitive, than having people typing search criterias into cells...

Now, your next assignment, should you choose to accept it...

Can you make it do the search again, from the result position (Fin
Next)?

(I'll try and figure it out myself, but that will take a couple o
years, he he. I used to do a lot of Jscripting and a little Java, bu
this vba is very confusing to me...)
 
How about this

Sub SearchForName()
Dim FoundCell As Range
Dim FoundFirst As Range
Dim CellWithName As String
Dim sFirst As String

With Worksheets("sheet1")
CellWithName = .TextBox1.Text
If Trim(CellWithName) = "" Then
MsgBox "Please type something"
Exit Sub
End If

With .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set FoundCell = .Cells.Find(What:=CellWithName, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not FoundCell Is Nothing Then
Set FoundFirst = FoundCell
Set FoundCell = .FindNext(FoundCell)
If FoundCell Is Nothing Then FoundCell = FoundFirst
End If

End With

If FoundCell Is Nothing Then
MsgBox "not there"
Else
Application.Goto reference:=FoundCell, Scroll:=True
End If
End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
It doesn't seem to change anything for me. It still searches from th
top when i click it again..
 
Do you mean run the routine again? That is not what I thought you meant.

Please describe the sequence of events that you want to follow, and what
will happen.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Oh yeah, I can see that it might be a good idea if i told you what
wanted before I asked you to help with it, he he

Iv'e got a customerlist with several thousand names, and I have m
sales rep's names with them etc.

I am trying to make it easy to type in part of a customer name, an
then just click the search button until the right one comes up.

If I fx know that the customers name is something with the word
"Xtreme" in it, but don't quite remember the whole name, I'll just typ
"xtreme" and then click search until I recognize the right record
(Like a "find next" thing)

Right now i can click several times, and it will only go to the firs
recognized record...


Did that make sence?

Regards,
Jørgen Lindegaar
 
Hi Jørgen,

Why should you be any different to other users?

Version 3 and counting!

Sub SearchForName()
Dim FoundCell As Range
Dim FoundFirst As Range
Dim StartAt As Range
Dim CellWithName As String
Dim sFirst As String

With Worksheets("Sheet1")
CellWithName = .TextBox1.Text
If Trim(CellWithName) = "" Then
MsgBox "Please type something"
Exit Sub
End If

If LCase(ActiveCell.Value) Like LCase(CellWithName & "*") Then
Set StartAt = ActiveCell.Offset(1, 0)
Else
Set StartAt = Range("A2")
End If

With .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
Set FoundCell = .Cells.Find(What:=CellWithName, _
After:=StartAt, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "not there"
Else
Application.Goto reference:=FoundCell, Scroll:=True
End If
End With

End Sub




--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
That seems to do the trick. Absolutely great.

I'm even beginning to se a pattern in the code. It's almost as if it'
starting to make sence to me, so not only did you make my worklif
easier, you also managed to insert a little knowledge in my poor littl
brain....

But don't worry, I'll be back online sooner or later, with yet anothe
I-have-tried-for-five-minutes-now-and-now-I-give-up-so-please-do-it-for-m
problem..

Ya'll behave now, ya' hear...

Jørge
 
kandinsky > said:
That seems to do the trick. Absolutely great.
Click to expand...

Great,. you've been a pain in the neck, but it's always nice to get rid of
pain (only kidding).
I'm even beginning to se a pattern in the code. It's almost as if it's
starting to make sence to me, so not only did you make my worklife
easier, you also managed to insert a little knowledge in my poor little
brain....

Double-whammy!

But don't worry, I'll be back online sooner or later, with yet another
I-have-tried-for-five-minutes-now-and-now-I-give-up-so-please-do-it-for-me
problem..
Click to expand...

Well we all look forward to that with fear and trepidation said:
Ya'll behave now, ya' hear...
Click to expand...

Does this indicate that you are from Texas?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding content of cell in visible "protected" sheets 4
find and paste 1
Set ws = ActiveSheet Compile error Can't find project or Library 8
Deleting rows 3
Find Value in Workbook 2
Cells.Find problem 3
deleting rows based on criteria 7
Foundcell 2

Back
Top