• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Send and Search buttons

J

jayzon

New Member
Hi


I am pretty new at this, so i have tried to modify code i have found online, and it works ok.

But i would like to make it a bit more advanced.

I want the data in the summary sheet to be moved to specific sheets based on the row cell, so if the value in the row cell is 1 its send to the row1 sheet and so on, and at the same time if the value in the Pre,cam,scan,An cells is set to ok, the data is to be copied to the Pre,Cam,Scan,An shetts.


I also want the search button to be able to fetch data from the different sheets based on the item number entered, and when fetched i want to be able to change the value in the Row,Pre,Cam,Scan,An cells press send and the item is send to the new sheets, and cleared from the old sheets.


Also when you press send the cells in the summary sheet should be cleared.


Thanks in advance


Link to sample


https://www.dropbox.com/s/tkxuyniwxntf61f/Rows.xlsm
 
Hi Jay ,


Some more detailing is needed I think.


1. A SEARCH button is required to be inserted in the SUMMARY tab.


2. A SEND button is to be inserted in the SUMMARY tab.


3. Normally the SUMMARY tab cells C3 , C5 , C7 , C9 , C11 , C13 , C15 , C17 and C19 will all be blank.


4. When data is entered in the C3 and C5 cells , and the SEARCH button is pressed , the data in C5 will be used as the sheet tab from which to retrieve data associated with the data in C3. The retrieved data will now be put in the cells C7 , C9 , C11 , C13 , C15 , C17 and C19.


5. When the SEND button is pressed , again the data in C5 will be used as the sheet tab ; on this sheet tab , the data from the cells C3 , C7 , C9 , C11 , C13 , C15 , C17 and C19 will be put in the first empty row , in columns A through I.


6. When the SEND button is pressed , as an additional action , the data in the cells C3 , C5 , C7 , C9 , C11 , C13 , C15 , C17 and C19 will be sent to the following sheets :

[pre]
Code: 
Stand -  if the cell C7 contains the text "OK" or "Ok" or "ok" or "oK"
Pre   -  if the cell C9 contains the text "OK" or "Ok" or "ok" or "oK"
Cam   -  if the cell C11 contains the text "OK" or "Ok" or "ok" or "oK"
Scan  -  if the cell C13 contains the text "OK" or "Ok" or "ok" or "oK"
An    -  if the cell C15 contains the text "OK" or "Ok" or "ok" or "oK"
[/pre]
Please confirm whether the Stand , Pre , Cam , Scan and An sheet tabs will have the same column headers as the sheets Row1 , Row2 ,....


Also , I don't see the necessity of having a column labelled Row in the sheets Row1 , Row2 ,... since this column will have the same number always i.e. the Row column in the Sheet tab Row1 will always have 1 in it , the Row column in the Sheet tab Row2 will always have 2 in it , and so on.


Narayan
 
Hi Narayan


There is a search and send button in the summary tab in my sample.


The header will be the same in the Stand , Pre , Cam , Scan and An tab and data send here is to stay stored here and not included in the search, the search is only for the row sheets.


You might be right about the row label in the row sheets.


Except that i want to use the same cells with the search button. So when i enter an item number in C3 in the summary tab i can either press send or search. If i press search the data will be fetched from the row sheets and entered in to the C5,C7,C9,C11,C13,C15 cells, the C17 and C19 cells is a timestamp and user info so they will generate automaticaly.


Should i post the code as it looks so far, or is it ok with the sample.


Jayzon
 
Hi Jayzon ,


Sorry , but the buttons were several rows down ! I see them.


I see that you already have the CommandButton1_Click and Worksheet_Change procedures ; have you developed these any further ? If you can post any additions / modifications you have made , it can help.


Narayan
 
Hi Jayzon ,


After your latest post , I have a doubt ; you say that after entering an item number in C3 , you can press the SEND button or the SEARCH button ; this can be either difficult or impossible !


If the cell C5 is to be used as the Sheet tab , then how can the SEND button be pressed , unless the cell C5 also has correct data in it ?


Similarly , if the SEARCH button is pressed after only the item number is entered , then searching for this item number in all the Row sheets will be more complicated. BTW , is it possible that the same item number can be present in several sheets ?


Narayan
 
Hi Narayan


I have latest changes in the workbook that is in drop box now, i made the headers in all sheets.


If i enter an item number in C3 and press search it is to change the row or other cells of the item, so there will be data in C5 and when i then press send the data should be send to either the same row sheet with new data in, or moved to a new row sheet if C5 has changed.


And thank you very much for your interest and fast response.


Jayzon
 
Hi Jayzon ,


Can you see what remains to be done in this version , and comment ?


https://www.dropbox.com/s/wkwza6x0v7btxhn/Rows.xlsm


Narayan
 
Hi Narayan


It works beatyfull, but there is a couple of details though.


When search i have to enter data in both C3 and C5, the C5 meens i have to know where item is in advance.


When i update any cell, the data is still in the old row sheet so if i change row to 2 and send its entered in row2 but still in row1 i am sure this will cause problems in next search for the same item, and double entries are made in the ok cells for every send.


In the ok cells only cam, scan, an are updated.


Still you are a true ninja, swift and with style.


Beautyfull work


Thanks for your time.


Your humble follower


Jayzon
 
Hi Jayzon ,


OK. Let me understand :


1. There should not be duplicate entries ; every modified entry should overwrite the earlier entry. Is this correct ? How would you define a duplicate entry ? Can I say that the item number cannot be duplicated ? Or is it a combination of item number and sheet tab ? What I mean to say is , can the same item number appear on different tabs ? I understand that the item number will be duplicated on the Stand , Pre , Cam , Scan and An tabs , but can it be duplicated on the Row1 , Row2 ,... tabs ?


2. When doing a search , you will enter only the item number ; we can make it so that if the tab number is entered , it is made use of , otherwise if it is left blank , then all the sheets will be searched ; is this correct ?


Narayan
 
Hi Narayan


If i understand you correctly yes to 2.


1.It can not be dublicateded in more than one rowtab.


There can be double entrys, but only in one rowtab and all the ok tabs.


Now every time a send is made the same item gets an extra row in the ok tabs.


I think one solution is to remove all item data in both rowtabs and ok tabs when a search is made, and then send the updateded data to new rowtab and/or oktabs.


Hope you understand what iam saying.


And thanks again


Jayzon
 
Hi Jayzon ,


Let us consider that an item number 1415 has been entered in , say Row7.


There is only one item numbered 1415 on the tab Row7.


Now , suppose you enter 1415 later , and press SEARCH ; if there is an entry with item number 1415 on the Row3 tab also , then which entry should be retrieved , the one on the Row3 tab or the one on the Row7 tab ?


Secondly , even if there is only one entry in the entire workbook , suppose the entry from Row7 is retrieved and displayed on the Sum tab ; now , will you modify the row number , and press SEND , so that the modified entry goes to another Row tab ?


Since you are time-stamping each entry , can we always retrieve the latest entry , according to the time-stamp ?


Narayan
 
Hi Narayan


All item numbers are unique, and should if possible only be in one rowtab.


Yes if there is only one entry in the entire workbook with a specific item number , i will search the item number and change the row and possibly one or more of the ok tabs and then press send.


I get your point on the timestamp, thats ok to go that route to, if the search only retrieves the latest entry for the specific item number.


Kind regards


Jayzon
 
Hi Jayzon ,


OK. I can work on this only tomorrow morning ; I hope you can wait till tomorrow.


In case you continue working on this , and make any modifications , please upload your latest version , so that I can work on that.


Narayan
 
Hi Narayan


Its fine you have already put in much more time and efford, than i could have hoped for.


I have to go to work now.


Thank you very much for your help.


Jayzon
 
Hi Narayan


Just got home from work, i have checked it out.


I have not found any problems, a very nice job indeed.


Thank youu very, very much for your time and interest.


Yours truely


Jayzon
 
Hi Narayan


If i type a non excisting item number and search, i get a run time error 13, type mismatch.


The hick up seems to be here.


If match_found = 0 Then

MsgBox "Item Number " & Sheets("Sum").Range(aSource(0)).Value & "has not been found on any tab", vbExclamation

Exit Sub


Jayzon
 
Hi Jayzon ,


Sorry , a mistake. Just replace that statement with the following :

[pre]
Code: 
MsgBox "Item Number " & Item_number & " has not been found on any tab", vbExclamation
[/pre]
Narayan
 
You must log in or register to reply here.
Back
Top