I'm going to show you how to create a custom search form I had a client this
week that received that received orders and then many weeks later they
receive the PIO for these orders so they'd like to search by the order
number and to put in a Pio number quickly without having to scroll all the
way through all the data that they have in their sheet so what I did is I went
to the Developer tab and I went to visual basic and I went to insert insert
forms so insert and forms and then I got user form 1 now for this user form I
created two fields one for the order number and what I do I did that is I am
is I use the use the tool box and the text is an A and I just drew this there
and then and so what I did is I formatted it with order nut error with
the order number as a label and then I change the font to cut Tacoma and I said
bold and you do that by clicking on here and you select the bold and in the de
como and it's size 11 and so then then to create the the form what I did is I
used the text box field and I created a text box and then I called this this
field order dot text now I did the exact same thing for the Pio number and the
Pio number text iron centered the buttons by clicking on the command
buttons on the toolbar box I drew the box out here and and I
labeled one insert and the next one new order so let's right click on the insert
button and then let's go view code and so here we have my code for it what I've
done is I have defined order as a string I
divide I defined pio as a string and I've defined range RNG is a range and
the row number as a string so what I've done as I said I've called order my what
I receive in my text box order dot order text text and then I started because I
have headings at the top of my sheet here I started as x equals two and I'm
saying do-while sheet one which is that one at that cells X which is 2 comma 1
is not as long as it's not blank then continue so then I set the range so set
range equals sheet 1 and I'm looking in the a column is where I'm looking for my
order numbers and then I then I'm telling it how to search okay and then
if range is nothing then put up a message box no order number was found
and then go to end II and D is just a location which is Pat out of the loop
and it's below just before the end sub ok now if it else so else if it did find
it then the row number that it fell roll number is equal to the range road that
if out founded on and Po number this is the Po number that we've received from
our box so I just assigned po-2 Pio number text which is that field and then
sheet 1 cells row number that we've determined from where the location 8 is
the column where the PIO numbers exist dot value equals Pio number it's
whatever you put in there and then I've increased it to the next one and started
starting to go over the the search again so how I'm displaying this is I've
created a little program and it's called sub show form and use your for what form
dot one show show an end sub and now what I've done is I
have inserted a button on my sheet and I have by right-clicking it then you can
assign it assign the macro so I have assigned the macro that I just showed
you okay so now let's run this so let's go search and let's find an order number
zero four zero one zero zero and let's put in a Pio number four or five six
seven eight nine okay and let's go and we'll just move it off the side so you
can see it and we'll go insert and there it has put in your Pio number for you
now in there certain circumstance they have many order numbers under the same
Pio so what we're going to do is I we've clicked this new order and it has simply
clicked this off and then we just put in another order number zero three zero
three six four and we go insert and it has put this in now to the code for the
order is just very simple it is for the other button it is basically just saying
order dot txt equals nothing so it's just telling it to clear that order
about text on the form so that's how you create a custom form please subscribe