Excel VBA Help - Referencing across sheets

[elmerg]

Hello.

I am attempting to create a workbook that is tracking information for a gaming group. I am trying to do the following:

In a single workbook, titled ‘Character Tracker,’ I have three worksheets at the beginning. Sheet 1 is titled MAIN, Sheet 2 is titled LIST and Sheet 3 is titled MASTER. MAIN will be where all information is put in and referenced from/to. LIST will have a list in the A column of every sheet that is created. MASTER will have a template for individual storage, with cells defined to give data into a print area that is formatted, and prints a character sheet. LIST is easy, no problems there, with manual creation of new tabs. I have all of the stuff working for the MASTER sheet, with getting the print area and formatting working and referencing the data from the cells elsewhere in the MASTER sheet. This wasn’t hard at all, since it didn’t involve really any VBA. The problems I’m having are all having to do with the MAIN sheet.

On MAIN, I need to do the following:
* Create a drop-down box at the top that pulls from the range AllSheets in LIST. If there is not a matching sheet name listed, it will then create a copy of MASTER and name it whatever was entered into the box. This name will then be added to AllSheets in LIST. (IE: I choose BAXTER, and it loads the cells from BAXTER. I type in ANDREW, and it copies MASTER and renames it ANDREW as a new tab/worksheet, and adds ANDREW to AllSheets in LIST).
* If the name exists in the drop-down box, then in cells defined below, pull all the data from the MASTER copy named in AllSheets in LIST. (IE: Choosing BAXTER grabs the information from the BAXTER sheet, putting them in appropriately-defined (or numerically-noted) cells on MAIN).
* Those cells need to be able to have new data (it’s either basic text or numbers from 1 to 5) put into them; this data change then needs to be reflected the appropriate sheet name chosen in the drop-down menu above. (IE: I choose sheet BAXTER, and change the Strength field from 2 to 3 in MAIN. BAXTER then needs to have the Strength field changed from 2 to 3).

Is this possible to do?

[rylo]

Hi

Can you put up a working example file that shows your current structure, the named ranges and some data. Put up some steps and what should happen in the workbook. That way we don't have to go around creating a workbook that may (or may not) really match your structure......

rylo

[elmerg]

Hopefully you guys'll be able to help me. I have attached a .zip file of the 'base' file (it's not my most updated version, but it's the only one I had with me while I'm here at work). It consists of four sheets inside of one workbook, Tracker.xls.

Sheet 1: Character Tracker v1.0
This sheet is where information should be entered to update the individual character information. At the top, there is a drop-down box that accesses the second sheet, pulling from a named range on the second sheet, LIST. This range is ALLSHEETS. What this page should do:
* If there is not a match to what the person is looking for in the dropdown box (IE: currenty it holds ADAM, ALEC, ELMER and MEGAN, and I want JOHN), whatever they type into the box should create a new tab at the bottom (see tabs, note one marked ALEC) named whatever is typed into the box (IE: if I type JOHN, it should make a new tab named JOHN) that is a copy of the sheet MASTER. This should also add that name into the ALLSHEETS range in LIST.
* If there was a name match (I choose ALEC from the drop down menu), then it loads the data from the appropriate tab (ALEC) into the appropriately-named spaces on the sheet (IE: the Strength field in ALEC copies to the STRENGTH field in Character Tracker v1.0.)
* Now that there is a tab (or was already a tab there for a name), the following must happen.
** Each boxed off area (Clan, Player, Strength, Blood Potency) can have data added into it. This data should, when entered, copy to the appropriate area of the sheet designated by the drop down menu (IE: I type 4 into Strength; the tab ALEC has the Strength changed to 4).

Sheet 2: LIST
This sheet contains the range ALLSHEETS, which should be affected as above.
* When a new name is entered from the data validation, it should add to this list. I would like to see alphabetically, but if that's not workable then that is fine.

Sheet 3: MASTER
This sheet is the template for each individual character storage.
* This sheet should be copied as a new template, each time that a new sheet is created.
** The new copy should be named whatever the entered name is (JOHN, WILLOW)

Sheet 4: ALEC
This is an example player storage sheet to use as a reference, though no data is entered into it at this time. This sheet is where individual character data is stored, to be updated through the 'main' sheet in order to save time from clicking through multiple tabs.

This seems like a lot, and it is. Hopefully someone can give me some pointers in the right direction, anyway.

Thanks a bunch in advance for any help you can give me!

Elmer Gilbert

[rylo]

Elmer

I don't see how you could do it all from the one drop down.

1) It would be possible to run an event macro from the drop down box that would fill the entries for an existing item.

2) For the add and update, I'd add 2 buttons to perform the actions.
(a) the first button would be to add a new item. This would add the name to the list and then create the output sheet.
(b) the second button would be to update the entered data.

So if you want to add a new person, you would press the add button, then select the new name from the dropdown, enter your data, then press the update button.

Would that work for your situation?


rylo

[elmerg]

Elmer

I don't see how you could do it all from the one drop down.

1) It would be possible to run an event macro from the drop down box that would fill the entries for an existing item.

2) For the add and update, I'd add 2 buttons to perform the actions.
(a) the first button would be to add a new item. This would add the name to the list and then create the output sheet.
(b) the second button would be to update the entered data.

So if you want to add a new person, you would press the add button, then select the new name from the dropdown, enter your data, then press the update button.

Would that work for your situation?


rylo

I didn't expect that it would be one dropdown menu. But, yes, what youv'e done works for the situation. You are awesome, man!

The only other thing I need to do is get a button (or something) that will print the 'print area' of every sheet in the ALLSHEET range (which I can probably do, I've been looking at printing stuff in VBA as I've been going).

Thanks for the help, it's great!

[elmerg]

Actually, there is one other thing I need specifically. A Delete Sheet button that will take the sheet chosen from the dropdown, and delete that tab and remove that name from the LIST sheet. I've been looking at the help, trying to use your stuff as an example, but... not sure about it.

[rylo]

Hi

Add another button to the sheet, update the caption to be, say, Delete Sheet and enter the following code for the button

Please Login or Register  to view this content.

rylo

[elmerg]

<SNIP!>

Nevermind, I managed to get it. Thanks for the help!