Excell data search, add if not found 1

[jgriffs]

Okay, I'm trying to get into Excell. In Lotus, with its macros, this was very easy to do. I'd like to see how it's handled in Excell:
A range(single column) contains a list of text items.
Open a dialog box which allows a text item to be entered.
By clicking "Okay", search the "mainlist" for that item.
If found, let the user know. If not found, also let the user know but also add it to the list.
Either result reopens the dialog for another user entry to check against the list. A "Cancel" button discontinues the process and closes the program.

 

[RobBroekhuis]

Get the text item from the standard dialog function inputbox. That's all you need, since you get an OK and a cancel button. Use columns("A&quot.find to see if the entry exists (it will return Nothing if it doesn't), and msgbox to let the user know either way. If not found, you can add to the bottom of the column using
range("A1&quot.end(xldown).offset(1,0) = userstring
Put the whole thing in a do ... loop while userstring <> &quot;&quot;.
That should get you started
Rob

 

[xlbo]

You will need
1: A named range - lets call it &quot;Data&quot; in A1:A50
2: A Form - lets call it fDataEntry
3: A button - bCallForm
4: Code on the button Click event
sub bCallForm_Click()
fDataentry.show
end sub
5: On the form you will need a textbox (tbData) and a button (bEnterData)
6: attached to the button, you will need code like:

sub bEnterData_Click()
dim myRng as range, myData as string
set myRng = sheets(&quot;Sheet1&quot.range(&quot;Data&quot
myData = fDataEntry.tbData.text
with myRng
set fCell = .find(myData, lookin:=xlvalues, lookat:=xlwhole)
end with
if not fCell is nothing then
msgbox &quot;Data Already Exists&quot;
exit sub
else
msgbox &quot;New data&quot;
lRow = sheets(&quot;Sheet1&quot.range(&quot;A1&quot.end(xldown).row
sheets(&quot;Sheet1&quot.range(&quot;A&quot; & lrow + 1).value = myData
ActiveWorkbook.Names.Add Name:=&quot;Data&quot;, RefersTo:=&quot;='Sheet1'!$A$1:$A$&quot; & lRow +1
end if
end sub

7: A cancel button would just have the code

fDataentry.hide

This should get you going anyway Rgds
~Geoff~

 

[jgriffs]

Geoff,
Here's where I stand at this point.
First, working entirely with Excell, the &quot;Form&quot; construction seems to be giving me hissy fits. In design mode, getting a form, putting a text block on it then going out of design mode the block disappears. Perhaps we can forget about that for the time being.
So I modified the enterdata sub by making &quot;myData=fDataEntry.tbData.text&quot; to &quot;myData=tbData.text&quot; Is that illegal? Would that change negate the &quot;.find&quot;
Placing the button and text block right on the Excell sheet seems to work.
I can enter a word in the text block then click the button (bass ackwards, I know)
clicking the bEnterData button presents the NewData message box.

It does not seem to recognize the resutlts of the find argument.
Questions: Does fCell have to be defined on the sheet somewhere or &quot;dim'd&quot; as myRng and myData are?
Is your line &quot;if not fCell is nothing then&quot; correct? Seems a bit of a double negative. I guess I don't understand the syntax.
John

 

[xlbo]

Well - you could try stepping thru the code with F8 and put a watch on &quot;MyData&quot; to see if it is picking up ok - it should be
fCell doesn't need to be dimmed but DOES need to be SET
If Not fCell is Nothing means if fCell is not nothing ie if fCell IS something ie it has found a match
That's just the way the syntax goes in this case

Can you post teh code you are now using ?? Rgds
~Geoff~

 

[jgriffs]

Geoff,
I'll post it a bit later, I'm home right now.
But. A thought I just had. If the Data consists of 6 digit numbers and first names (text), I have to format the Excell range as text. That may be why the Find (which is defined as text) isn't finding my input item. Hmmmm? But if, after adding my entry to the bottom of the list (that does work fine) and I click on the button again, it will add the same entry to the list again. That means it's not finding it's own addition (which should be text, right?)
The button sub : Code on the button Click event
sub bCallForm_Click()
fDataentry.show
end sub
And clicking the button an error occurs saying the object does not support... I'll have to get the exact for you. Like I said before, unless required, I'd just as soon have the entry text box and button be on the Excell sheet at all times.
When you say a button called bCallForm, in the buttons properties would the name include the &quot;b&quot;? I assume so.
Finally, might we communicate of the forum? Your option.
Thanks for the continued discussion and help.
John