Hi – I’ve created a form, from which I want to search and replace information into several sheets. Ex: When I select the button ‘find record’ after entering a name, I want it to search through consecutive sheets to find the name and populate all information associated with that name into the form. From there, I would like to be able to edit that particular record’s information in my form and then select another button on my form ‘update record’ to have the information automatically replaced back into wherever it finds that record’s name in the sheets.
Find, View, And Replace Values With A Form
-
-
-
Re: Find, View, And Replace Values With A Form
My recommendation would be to maintain only one sheet to avoid duplicate information. This will greatly simplify the routine to find and update a record In that one sheet at the top of each column place a button that will hide all records except the desired ones.
Example: At the top of the Status column Place a button with the caption name “Status”. This will identify the column. When the button is pressed it will display a form with a list box listing the status codes. When a status code is selected all records not this code will be hidden. Another button in the header could be used to redisplay all records.
If you need to keep track of the date of each status code then either use multiple columns or multiple rows wit the same associate name.There are many, many was to format this. The important thing is to avoid multiple entry of the same data such as Associate’s name.
hope this helps
Bill
-
Re: Find, View, And Replace Values With A Form
Bill Rockenbach - Thank you
At this point I'm not able to change the sheet (but trust me I tried to convinced the staff).
The whole logic behind the update I'm trying to implement is very simple. However, I don’t have much VBA exposure.
Also, to be more detailed - The find button only needs to look at the main sheet to locate the record and all the information is now displayed in the form. If the staff makes any changes and hits the "Update" button, at this point, the macro needs to search through all but 2 sheets and replace what’s there with the information from the form.
Is this even possible?
I appreciate your help.
-
Re: Find, View, And Replace Values With A Form
You first have to find the record.
Then from the record found update the user form.
Then from and additional update button the user can update all the sheets.
The sheets can be update one at a time or through a loop.I re-wrote your button4 code.
This will get you startded.
Code
Display MorePrivate Sub CommandButton4_Click() Dim Associate As Range Set Associate = Nothing Set Associate = Sheet13.Columns(1).Find(TxAssociateName.Value, _ LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True) If Associate Is Nothing Then MsgBox "Associate " & TxAssociateName.Value & " record not found.", , "Record not found" Exit Sub End If Sheet13.Select 'This message just to demonstrate that the Find statemet returns the address of the first cell in the range. 'Use the row of the address to locate the record. MsgBox "Associate " & TxAssociateName.Value & " is located at Row: " & Associate.Row 'Put code here to fill the fields in the form. 'Put an update button on the form so the user can verivy the correct record found 'change any data, then update all the sheets. 'Put the sheet update code in the "Update" button procedure. ' do not Unload Me End Sub
I suggest you get in the habit of dimensioning your variables and using Option Explicit.
Bill
-
Re: Find, View, And Replace Values With A Form
Bill Rockenbach - Thank you.
The code you posted works great. I'm still a little clueless on how to do the rest, but you have explained correctly in you code you posted.
Any ideas on how to get that done?
-
-
Re: Find, View, And Replace Values With A Form
I put together a sample attached. Eliminated all the sheets in favor of buttons at the top of the main sheet. Pressing teh buttons will make th escreen look like the different sheets you hade. If you still want all those sheets then take my code and alter it to fit your situation.
Bill
-
Re: Find, View, And Replace Values With A Form
Bill Rockenbach - Thank you
I'm going to have a talk with the staff to see if your suggestion will meet their needs (I have no clue why they need to have a record in multiple sheet).
The sheet you made looks and greats great.
There is only one error I got which is:
Run-Time error '16':
Expression too Complex
This occurs everytime I click on any button other than the "Record".
Thank you
-
Re: Find, View, And Replace Values With A Form
Here is the code like that it shows me when I hit debug
-
Re: Find, View, And Replace Values With A Form
Why not simply use AutoFilter?
-
Re: Find, View, And Replace Values With A Form
royUK - I'm very new to VBA, and I just played around with the filter option and it was not working for me at all.
Any suggestions?
Thank you
-
-
Re: Find, View, And Replace Values With A Form
Hello again nightkil,
I have no idea why that line of code does not work. I’m using x2000 and never had a problem with that line. I broke the code into two lines, maybe this will work for you. What version of xl are you using?
I did not suggest auto filter as it appears the users want a one button (or one sheet) solution. However, auto filter is an excellent way to go if the users are willing to use it. It can filter down to a finer selection as needed.
Attached revision.
Bill
-
Re: Find, View, And Replace Values With A Form
Bill Rockenbach - Thank you
Everything is working great now.
I use excel 2000, 2003, and 2007 (in the weekends). Most of the users will have 2003.
Thank you for your help
-
Re: Find, View, And Replace Values With A Form
Glad to here itys working. You need to write code for the Delete button and propably put in some edits for data entry. Also add more custom buttons for the Units.
This approach is not recommended for xl programs more complicated than yours. A better approach might be the Auto Filter as royUK suggested.
If you want to use the auto filter then:
1. Select the columns you want the auto filter to apply to
2. Click on the DATA menu
3. Select Filter
4 Select AUTO FILTERYou should see a down arrow in the top cell of each column you had selected. Click on the down arrow and a drop down list of all items in that column will appear. Select an item. All rows will be hidden accept the item selected. You can further zero in on an item within the item you just selected by filtering another column. Clicking on the down arrow again will bring back the hidden rows.
To cancel the auto filter repeat steps 2 through 3 above.
This is a great xl built in tool if your users are willing to use it. It could replace the selection buttons I customized.
Bill
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!