+ Reply to Thread
Results 1 to 1 of 1

Worksheet dedicated to Edit a list of products found in other worksheets

  1. #1
    Forum Contributor
    Join Date
    05-19-2012
    Location
    Croatia
    MS-Off Ver
    Excel 2007
    Posts
    200

    Worksheet dedicated to Edit a list of products found in other worksheets

    LIST-HR.xlsm
    ATTACHED IS THE FILE IN QUESTION
    Protected by Password: 1234

    Hello!

    I created a worksheet dedicated for the sole purpose to EDIT a list of products ("publication") or a category ("headers"), which are found in all the other worksheets. Now I just need it to actually work. I realize that I am asking for a bit much, perhaps you can help with one of the features mentioned below. It would probably be wise to begin with the HIDE or DELETE button first. Please help!

    In the attachment, this worksheet for editing the products and categories is: (Sheet4 "Uredi popis")

    ADDING: (in Sheet4 "Uredi popis" Range "A2:H3")
    I already made all the buttons, dropdown menu, textboxes and criteria for the user interface as seen in attachment (Sheet "Uredi popis" Range "A2:H2"). And there are two dedicated worksheets with sources for this to work, which is (Sheet5 "#" and Sheet7 "#2"), the only thing left is coding, which I need your help with. The code could work perhaps something like this:
    1. Add new row to all the following worksheets (Sheet1 "Arhiva", Sheet2 "Naručiti", Sheet3 "Inventura", Sheet4 "Uredi popis", Sheet6 "Ispis", Sheet7 "#2")
    2. Go to the source (Sheet5 "#"), copy Cols "A:BZ" ; it's all labeled, there's a dedicated row for each worksheet. They contain all the necessary coding, formatting, etc. for both the "publication" and "header"
    3a. After copying, simply paste the corresponding row to the corresponding worksheet. Every worksheet displays the same "publication" and "header" in exactly the same row, so that should make it easier for programming. Doing that it will also copy all the coding, formating. Please make sure the code preserves the format colors fonts and row height.
    3b. The only difference between "Publication (SI)" and "Publication (SRI)" is what is pasted to only (Sheet2 "Naručiti"). If "Publication (SI)" is selected, then it copies (Sheet5 "#" Range "A11:BZ11"); or else if "Publication (SRI)" is selected then it copies (Sheet5 "#" Range "A12:BZ12").
    4. Post a "JUST ADDED" message on Sheet4 ("Uredi popis") in Col "H" as example is shown in attachment. This message can remain until the file is re-opened again.
    5. Of course, you can remove the sample data currently in the TextBoxes. Also, just so you know the "source link" for each textbox are right below them in row 4. However, the combobox' input range relies on the just added row found in (Sheet7 "#2"), so I believe that the input range for this combobox will automatically increase +1 row upon adding witout any additional programming.

    EXAMPLE OF THE "ADDING" FEATURE: (mentioned above; also shown in the attachment)
    A. In the worksheet (Sheet 4 "Uredi popis") the user wants to add an "SI" publication (Number: "5113" and Name: "New Ages Booklet") below the already existing header in row 6 "GODIŠNJA IZDANJA".
    B. So in the first combobox he specifies "Publication (SI)"; in the second combobox he specifies "Below"; in the third combobox he specifies "GODIŠNJA IZDANJA"; below that in the first textbox he types in its number: "5113"; in the second textbox he types in its name: "New Ages Booklet" and finally he clicks on the "GO" button, which activates the code you write.
    C. Then your coding went to work and:
    (as mentioned above 1.) adds a new row in all the worksheets except (Sheet5 "#");
    (as mentioned above 2. & 3.) copies (Sheet5 "#" A6:BZ6) and pastes it in the newly added row 7 in (Sheet1 "Arhiva");
    (as mentioned above 2. & 3.) copies (Sheet5 "#" A11:BZ11) and pastes it in the newly added row 7 in (Sheet2 "Naručiti");
    (as mentioned above 2. & 3.) copies (Sheet5 "#" A17:BZ17) and pastes it in the newly added row 7 in (Sheet3 "Inventura");
    (as mentioned above 2. & 3.) copies (Sheet5 "#" A22:BZ22) and pastes it in the newly added row 7 in (Sheet4 "Uredi popis");
    (as mentioned above 2. & 3.) copies (Sheet5 "#" A27:BZ27) and pastes it in the newly added row 7 in (Sheet6 "Ispis");
    (as mentioned above 2. & 3.) copies (Sheet5 "#" A32:BZ32) and pastes it in the newly added row 7 in (Sheet7 "#2");
    (as mentioned above 4.) shows the "JUST ADDED" message in (Sheet4 "Uredi popis" cell 'H7') and hides this message when the file is opened again
    (as mentioned above 5.) adjusts the input range for the combobox in (Sheet4 "Uredi popis") to be +1 row, from the range before: '#2'!$B$6:$B$85 to the new range after: '#2'!$B$6:$B$86

    IF the user wanted to add an "SRI" publication. This would be the only difference in the above example:
    Instead of the above red italicized functions, it would be replaced by the following blue functions...
    - B. he specifies "Publication (SRI)";
    - C. copies (Sheet5 "#" A12:BZ12)

    DELETE BUTTON: (in Sheet4 "Uredi popis" Col "A")
    Please program a code to make this button work. Clicking the red "delete" button basically just needs to delete the same row number in all the worksheets, however it does nothing to (Sheet5 "#"). Since the "publication" or "header" is found in exactly the same row in every worksheet, it will work! And perhaps the "input range" for the combox in (Sheet4 "Uredi popis") will automatically adjust to (-1 row) without any additional programming.

    HIDE & UNHIDE BUTTON: (in Sheet4 "Uredi popis" Col "B")
    Please program a code to make this button work. Clicking the "Hide/Unhide" button does the following:
    1. Basically just needs to hide or unhide the same row number in all the worksheets, however it doesn't do that for (Sheet4 "Uredi popis") and (Sheet5 "#").
    2. Displays a status message on Sheet4 ("Uredi popis") in Col "G" as example is shown in attachment. This message can remain permanently.
    3. Also the "Hide/Unhide" button needs to change names as it goes from one status to the other. Also shown as an example in the attachment.

    OTHER REMINDERS: Important stuff
    1. The worksheet (Sheet5 "#") dedicated to sources for various codes and functions, is also there for your new codes. So feel free to utilize this worksheet for the end result.
    2. (Sheet4 "Uredi popis") has all the source text for the name and number of each "publication" or "header" name. And all the other worksheets feed off of it using the (Paste Link) function. This makes it possible to edit one source, and affect all the worksheets at the same time.
    3. (Sheet4 "Uredi popis") I basically just need your help coding this sheet only, to control all the other sheets as mentioned before. So, when you're adding a "publication" or "header" just have the "TextBox" send the information about number and/or name to this page. All the other pages will copy the name using the (Paste Link) feature already implemented.
    4. (Sheet5 "#") This is the only other worksheet you may need to work on a little. Right now the source for adding a "header" and "publication" to the (Sheet4 "Uredi popis") is here in rows 20 and 21. To be exact: 20 iz the "header" and 21 is the "publication"... it's setup the same as (Paste Link), however, I don't know if this will help. If so, then just remember to edit the coding in these rows, too, for the Paste Link feature to work when ADDING a new "publication" or "header" since it relies on this worksheet.
    5. Also, keep in mind that all your code has to be able to function while the workbook and all the worksheets are "Protected" or Locked... So, please test the functions while everything is in "protected" mode. To unlock it use Password: 1234 But it may be annoying as you're working on it, because it is somehow programmed into the VBA workbook to lock itself each time you make a move. If you want, you can remove this code, and put it back in when you're finished.

    Please help!!
    Thanks!

    LIST-HR.xlsm
    Protected by Password: 1234
    Last edited by nenadmail; 05-25-2012 at 08:00 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1