[armandh]

Hello there and thank you in advance for your help.

I have been researching this problem for awhile and have not been able to find a solution anywhere. I know its probably simple but Ive spent hours and hours trying things and nothing.

Here is what I am trying to do..

I want to add a List Box that is linked to the User field to the above table with 4 check boxes for each column in the table. Also a text box and two Command buttons. I want to have it so that I can select multiple users in the list box and add or subtract the value, depending on which command button I press, in the text box to whatever columns are selected in the 4 check boxes. Ide also like the text box to clear after the command is executed.

Example:

A1 - A4 are users 1-4

I select column 2 and column 4, with users 2 and user 4 in the list box.

Both users have a value of 200 in both columns

I enter 100 into the text box and press the subtract button.

The value of user 2 and user 4's column 2 and 4 will then be 100 for each cell.

*Note* I want it to subtract or add the number from the individual cells ONLY when the command buttons are pressed telling it to do so.


Again, thank you for any help!

[Keebellah]

Welcome to the forum.
I think you should attach a sample file with dummy data. and mark the example there.
I think it can be done with some vba code.

[armandh]

Ok, here is what ive been working on. The other tabs are the actual data that I want to apply what Im asking for help with. The dummy tab is a mock up of what I want done.

As you can see, a list box with users 1 - 4. Then data for each user. I want to be able to subtract or add any value to individual or multiple users data at a given time simply by checking which data column and selecting users from the form, inputting a value into the text box, then hitting add or subtract.

Also, if you see the ICC 10 and ICC 25 tabs, thats where I am going to be putting it. So maybe if you could explain how I could edit the VB code to expand the features of what I want for future expansions?

Sorry to be asking for so much. Im not familliar with VB whatsoever. lol

Much appreciated.
Armand.

[Keebellah]

I picked up the file and will see what I can do for you

[Keebellah]

Hi Armand,
I have some code I found and editted to my needs that could do the job for you, but I have the following questions:

The list of users (number of rows) is that variable?
The number of values (number of columns) is that variable too?

The routine I am thinking of could be used for any ammount or rows and any ammount of columns and then query what value you wish to either add or subtract, or maybe even devide or multiply.

I have it in my head butbefore a start playing with it let me know.
I mean, no cure, no harm done but I like puzzles and programming so ...

Let me know, okay?

[armandh]

The list of users (number of rows) is that variable?

Yes the list of users may grow quite large. Even in the hundreds.


The number of values (number of columns) is that variable too?

The number of Columns may grow or shrink depending. But it shouldnt become larger then six or so.


And thank you tons for the help. Dont know what I woulda done without you! =)

[Keebellah]

Hi Armand,
More questions:
In your sample you have sheets ICC 10 and ICC 25, I expect that these are the sheets where you want to apply the macro to?

The column data starts on row 4 and the Playesr at column B
Will this always be like that?
The routines I am going to try will de row and column bounded so I have to know if this will be the default layout:

Colum B starting Row 4 down the name of the players

Row 4 starting Colum C to the right the Items (now 4 columns + one extra Column Total)
In the cells below I expect the values, correct?

As you mentioned the player list can be indefinite (Excel maximum (2003 = 65536 rows, 2007 and up 1048576 rows)
Columns max is for 2003 = 256 and for 2007 and up 16384

You want the module to give you the choice of one or more players and then one or more columns and apply either a sum or subtraction to these combinations?

If this is correct I'll get the routines fixed for these basics and then offer you a prompt of the sheet, the player orplayers and the columns

Okay?
All you have to do is be a little patient, I have it in my head but now edit the code accordingly, and it's bedtime now for me here (23:17)

Read your reply in the morning.

[armandh]

Yes thats correct, I want to be able to select multiple users via a list box, then the column for those players with check boxes, then input a number into a text field and press one button to add or subtract that number from multiple players. essentially having the ability to add or remove a fixed number from each individual cell at any given time within the table.

the sheet icc 10 and icc 25 will be where I would like the form to be. Each indapendant to that sheet.

[Keebellah]

Okay, one more small issue, you have autofilter on and a subtotal row at the bottom. It could be that I cannot rebuild the subtotal because of the system's language difference.

I also saw that you hav aan add player button, Maybe I'll have to edit that routine.
I'll fool around and send you the result when it's done (give me a little time)

[armandh]

Yeah its No problem take your time. Thanks a lot for the help!

[Keebellah]

Hi Armand,
I did some coding and put some info on the 'dummy' sheet.
It's all working under Excel 2003 and I don't expect that there will be any problems with 2007.
I did have some problems with your macro's because you have used 2007 specific formatting which I can't handle.
(adding a new player)

Take a look at the dummy sheet and see the add / subtracting work

The way I built it you could add numerous ICC ?? sheets and the code will still work, but that part of adding new sheets I haven't done.

The part you coded works fine but one tip is to make it more general, like if you add a new ICC sheet you would have to edit Sheet1 to contain the new 'link'

Play around with it, see if it does what you want.
I suggest the the 'dummy' sheet become the actual recalculation sheet, but ... it's your game.

[Keebellah]

Hi Armand,

I ran into some small issues so I did some editting.

The corrections can be found in the attachment.

I don't understand what you mean by decay and those values on sheet1 and the use is for the tables.

I did however see that these are dependant on specific ranges and therefore difficult to cover by code

I think the functions sheet could be used for basic information and then the whole workbook could become independant of how many ICC ??? sheets you have and become very flexible.

For now let me know it the basics work.

If you can test and see what happens when you add a new player and if the integrity remains unchanged then that part is covered.

Happy gaming!

[Keebellah]

Hallo Armand,

I haven't seen any reaction to my last post.
Let me / us know if there are any issues, I have been playing around with the last Feb 11 version and edited some stuff.

If you would like to see it let me know.