VB Script to replace Conditional formating

[2k05gt]

I have a sheet that has 3 rows and 20 columns grouped in blocks of 9
(the attached sheet will describe morein detail)

This is a Drag Racing Team Points and Ranking sheet (large portions were removed because of size limitations)

Group 1 is Best ET and it has 3 rows Best ET1, 2 and 3. there are 3 columns for Visit 1, same for Visit 2, 3, 4 and 5
I am trying to make a VB Script to only allow 1 selection per group, forinstance if I enter a 1 in row 1 column 2 the (Best ET 1) will highlight and then Lock all blank cells in Visit 1 column, and the entire Best ET 1 Row through visit 5. This says that you used up your
first Best ET points (you are allowed a total of 3 for the season)

this is repeted for Group 2 (Best 60ft), Group 3 (Best light)

Any Ideas on how to make this?

[2k05gt]

I have been searching for the last 2 days and have not come up with a solution.
I wish I was stronger with VB.

In my many searchs on this site it looks like this issue tends to go unsolved,
I have found several un answered questions like this, is this that dificult?

Well the search continues

[MarvinP]

Hi 2k05gt,

Your workbook is very pretty but not that functional when using Excel. We see lots of different arrangements of data on single sheets, on multiple sheets and even on different workbooks.
On your single worksheet named SM, you have blocks or ranges of data based on Car Number. The row names in each block are the same and the column names in each block are the same. This helps only a little.

Excel works best in TABLES (in 2003 they were called LISTS) of data. Each row is a record of data and each column is a different field in the record.

In newer versions of excel this paradigm has been enhanced and improved. Filtering and Sorting of TABLE data is easy. AutoFiltering and sorting of data TABLES has improved. Pivot Tables and Pivot Charts of TABLE data has improved with newer versions.

Because your data is pretty and not is an Excel Table format two things happen.
1. You are proud of your view of data with colors and arrangement.
2. You are less inclined to change the arrangement of the data to fit with the Excel TABLES feature.

I believe you should study all the features you get from developing a real Excel Table and change the structure of you data to fit that paradigm.

see for a start
http://www.jkp-ads.com/articles/Excel2007Tables.asp
and then search the net for "Excel Table Examples" to see how you might restructure your data.

[2k05gt]

the workbook is actuall very large there is a RANKINGS sheet that tallies the totals then puts the driver in 1st place, 2nd place and so on, based on time slips submitted, each driver gets a PDF of their Visit / Run sheet, in this case it's the "SM" Street Mod, There is also a "TM" Track Mod sheet and a "PM" Pro Mod Sheet. I am helping out the guy who has been running this Online Racing Team for years now. I said I would take a look at what he has and try to make it less work for him to enter the totals, I Agree with the look you described but it worked well for the Manual entries he was doing. the more I try to improve it, the more headaches it's giving me

I looked at the link and I decided to fix the sheet, it was complicated so I told my friend I was going to redo the entire thing, so here is my attempt to fix it. See attached

[2k05gt]

Hi 2k05gt,
I believe you should study all the features you get from developing a real Excel Table and change the structure of you data to fit that paradigm.

see for a start
http://www.jkp-ads.com/articles/Excel2007Tables.asp
and then search the net for "Excel Table Examples" to see how you might restructure your data.

While the Table option is fine for someone thats is using the sheet for collecting data, sorting and doing sums, it does not work well foe a sheet that is being used more like an application. As I stated in a previous post, I did not create the workbook, I am trying to make it more automated for the person that uses it. Since I amn just helping out I did not want to spen alot of time rebuilding his entire worksheet structure, but I have made changes to make it eaiser to use functions and VB code.

He designed it to look like the Paper cards used at Drag Racing events, so it would be used by someone who did not know excel or learn a new layout, so the creater said not to change the layout.

I removed all the Merged cells first, thats a pain, I got rid of the colors, that too was a pain.
So all I was asking for was a little help in figuring out how to do a peice of code and I get a written lashing on what a crappy layout it is.

So again I will pose my origional question...
in post number 1, CAN SOMEONE HELP or am I just going to get berated?

[TMS]

You're not being berated ... yet. but there's time.

You commented that you'd found several similar posts which had gone unanswered or, at least, unresolved. The reason is that there's only so much you can do with a structure that is designed to be easy on the eye, but not well put together as far as Excel is concerned.

Rows and columns of data work well. Blocks of data spread across the worksheets and workbook do not. That makes creating formulae and/or VBA code difficult and time consuming .... so it doesn't happen.

Regards, TMS

[2k05gt]

You're not being berated ... yet. but there's time.

You commented that you'd found several similar posts which had gone unanswered or, at least, unresolved. The reason is that there's only so much you can do with a structure that is designed to be easy on the eye, but not well put together as far as Excel is concerned.

Rows and columns of data work well. Blocks of data spread across the worksheets and workbook do not. That makes creating formulae and/or VBA code difficult and time consuming .... so it doesn't happen.

Regards, TMS

An I understand this, but my data is in rows and columns, It is not all over the place,

we are talking about a simple setup here.

5 groups of 9 cells (3 across, 3 down) Rows 1-3 and columns A - O

think of it as Tic-Tac-Toe squares 5 lined up next to one another
and 2 more rows below them

\1

Now when a user clicks on the Cell, an X is placed in it and the row is done and no more X can be put in it as well as the column with the X

\1

You are alowed on y 3 X's one in each row for each group best ET 1, 2, 3 (MAX 3 X'S)

\1

I am doing now with conditional formating but I have to repeat the same sequence
20 times on the same sheet and Copy/Paste does not come the Formula data in conditional formating.

[MarvinP]

Hi 2k05gt,

I almost understand the question now. Excel doens't have an easy way to "lock out" cells from data being input unless it is the entire sheet. The term in Excel is called Protect.

Find the attached with something close to what you may want. Double Click in any cell between C16 and Q24 and it will background that row. Double click on an x and it will remove the x and uncolor the row.

Is that what you wanted? It is done using the following code. See the attached.

Please Login or Register  to view this content.

[2k05gt]

Yes, the row highlight is correct.
but is there a way to do include the range of cells that the X is in?

For instance, If I clickd on cell E17 the entire row 17 is highlighted, the driver can not get more points from that row, it's done... but the Columns C16 though E18 are also done because the driver can not receive more that 1 Best points for ET in one visit.

so basicly the row is done as well as the tic-tac-toe block of cells for ET.

Please Login or Register  to view this content.

I attached the sheet with your code, my addition with the 3 max and the conditional format that works with your VB
on what I am trying to do in VB


I added the ability to limit the X to 3 for the ET Group, but can't figure ouy how to look throught other catagory items (60 and light)

Please Login or Register  to view this content.

Anyway to put that if workstationFunction.countif ( into the if target section?

[MarvinP]

Hi ,

Now you are starting to see the difficulty in programming what you want.
For example:
1. What if an x is in C16 already and the user puts an x in D17. What should happen?
2. If the user puts an x in C17 you want to color/block C16:E18. How do I code that?
3. If/when Driver 1 is done and correct, how do you code this for all the drivers below driver 1.

See if you can see what my code does and let me know how to determine what block of 9 cells the x is in and color it.

Also - I think you changed your second .jpg from before in that it had two x's on the same column. I didn't think that was allowed.

[2k05gt]

Hi ,

Now you are starting to see the difficulty in programming what you want.
For example:
1. What if an x is in C16 already and the user puts an x in D17. What should happen?
2. If the user puts an x in C17 you want to color/block C16:E18. How do I code that?
3. If / when Driver 1 is done and correct, how do you code this for all the drivers below driver 1.

See if you can see what my code does and let me know how to determine what block of 9 cells the x is in and color it.

Also - I think you changed your second .jpg from before in that it had two x's on the same column. I didn't think that was allowed.

Sorry that was a typo on the JPG, you are correct it's not allowed. ( in racing thats "double dipping")

1) once an X is placed in C16 the user should not be able to enter an X anywhere in within the block from C16 : E18 $ C16 : Q16 thats why I was trying to firgure out how to protect the cells

2) I origionally was thinking about just putting X's in the blank cells and letting the user enter a 1, this would provide VB the variable to protect cells with X's in it. ButI see what you mean. I may or may not go with colors depending on complexity. it was just an idea since the guy who created the sheet would highlight the line so it would stand out. I may have the Rankings page list the drivers Best there when it comes to Awards time. People like Trophies

3) And this has been the one peice that is causing me the most pain, I was able to put the Ranges in the code to allow for that with the double click "X"
but If I want to use my other code with yours it will not let me enter multiple ranges, but it would solve the range vs how many X can exist in that range.

I also thought about a Do Loop and go through a DIM set of ranges but I have not been able to come up with one that wourks with my limited knowledge of VB.

Here is a version I am playing with now with no sucess I might add

Please Login or Register  to view this content.

Finally,
I have thought about making one sheet per driver and have the RANKINGS sheet be the main page, the user can click the drivers name and a popup would allow him to enter the data. but that would be 60 sheets
Last year I was going to attempt to update this thing in Access but I ran into issues there as well. I am not a very good programmer, I am a network security engineer and Hobbiest Drag Racer.

[2k05gt]

I was able to figure out (#3) how to do each row for each driver,
and that is with list code I found

If Left(Range("B" & Target.Row).Text, 3) = "New" Then

If I search for the Text "New" then I and work with those rows
but this code needs to be refined, it's clitchy since it is a combonation of yours
and other things I found.

Please Login or Register  to view this content.

The Set rng = Range was to validate that there are only 3 X's per each 3 catagory rows
and it is not doing what I had hoped.
Still plugging away at it, I have spent more time than I wanted but I hope to have somthing
for the Team that is worthwhile.