Checkboxes vs Drop Downs

[usnapoleon]

Hello,
I could use an honest opinion on a project before I expend time and energy into it. I was on this forum recently getting help in making a spreadsheet where input is heavily involving checkboxes. A single tab looked like this:

There are 31 tabs total, to represent the days of the month, and 37 rows of the checkboxes per tab. One thing I noticed is that the spreadsheet was slow, and likely due to all the checkboxes.

The spreadsheet works by checking 1 box on the Department area, and one box in the Voucher Type area. The black areas get populated with the checkbox-selections, and there are sumif formulas above that review the black areas and the amount $$ in the far right and bango, we have an easy-to-input spreadsheet that gives us our info. The whole intention was to make the input area easy with the use of checkboxes. It's been much easier, well received, but its nagging me that it's a slow spreadsheet, and modifying it (adding/removing checkboxes) is a pain in the butt.

What I'm envisioning is this...

We have 1 'input' area, which utilizes 2 dropdown menus you see at the top, and the amount is a simple input. you press a button, which I called above the 'post' button... and it will post the data in the area below - I color-coordinated the input sections to where they get posted, just as a visual aid. The formulas I mentioned will do sumif's based on the posted area, no biggie here. Each time you 'Post' it will apply the selected items to the next empty row. You can make edits after-the-fact, or clear data altogether if you want to redo it, etc.

• So if you clear row 30 because of an input-accident, you can highlight the row, clear it, and the 'post' button will re-use row 30.
• Or maybe you didnt see the input error initially, and you get to row 35 before you caught it.
  • You can delete the row entirely, which will bump up all the other rows... so 32 becomes 31, etc etc, and 35's the next blank row and your 'redo' goes there.
  • Alternatively, maybe you just cleared out row 30, so rows 29, 31-34 are populated but 30 no longer is, the Post will go there, then the next one will resume at row 35.

My questions to everyone reading...

1. is something like this possible?
2. will it be a faster spreadsheet?

Thank you for your time!

 

[My Aswer Is This]

When I look at your image, I cannot tell what columns we are dealing with
my script assumes you want cells in columns 3,4,5,6,7,8,9,10,12,13,14,15,16
to turn blue when double clicked on and the offset so then x number of cells to the right.

 

[usnapoleon]

When I look at your image, I cannot tell what columns we are dealing with
my script assumes you want cells in columns 3,4,5,6,7,8,9,10,12,13,14,15,16
to turn blue when double clicked on and the offset so then x number of cells to the right.

Hello, sorry for the delay while I was out for the weekend.

Here is a current snip so you can see the columns:

And the current code is exactly as you modified it:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
Application.EnableEvents = False
 Dim ans As Long
 ans = Target.Row
 
Cancel = True
If Target.Row < 22 Then
MsgBox "You must double click on row 22 or Greater" & vbNewLine & "You Double clicked on row " & ans & vbNewLine & "The script will now stop"
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
End If
    If Target.Row > 21 Then
        Select Case Target.Column
            Case 3: Target.Offset(, 8).Value = "CG": Target.Interior.Color = vbBlue
            Case 4: Target.Offset(, 7).Value = "MP": Target.Interior.Color = vbBlue
            Case 5: Target.Offset(, 6).Value = "PH": Target.Interior.Color = vbBlue
            Case 6: Target.Offset(, 5).Value = "BS": Target.Interior.Color = vbBlue
            Case 7: Target.Offset(, 4).Value = "MS": Target.Interior.Color = vbBlue
            Case 8: Target.Offset(, 3).Value = "SH": Target.Interior.Color = vbBlue
            Case 9: Target.Offset(, 2).Value = "TH": Target.Interior.Color = vbBlue
            Case 10: Target.Offset(, 1).Value = "ACT": Target.Interior.Color = vbBlue
           
            Case 12: Target.Offset(, 5).Value = "RW": Target.Interior.Color = vbBlue
            Case 13: Target.Offset(, 4).Value = "FV": Target.Interior.Color = vbBlue
            Case 14: Target.Offset(, 3).Value = "SB": Target.Interior.Color = vbBlue
            Case 15: Target.Offset(, 2).Value = "TH": Target.Interior.Color = vbBlue
            Case 16: Target.Offset(, 1).Value = "ACT": Target.Interior.Color = vbBlue
       
        End Select
    End If

Application.ScreenUpdating = True
Application.EnableEvents = True


End Sub

Everything we want to happen with the offset is going correctly. Your script addresses them perfectly, so your assumptions were all correct, and I have no issues understanding the Offset.

Now we just need to address user mistakes. There can be 2 types: 1) clicking a cell by mistake and 2) changing your mind and wanting to click a different cell.

Mistake #1
How can we do an 'undo'? If a cell is blue, and it's double-clicked again, we want it to go back to default non-filled.

Mistake #2
Using a part of the above snip as an example

If a cell is clicked, such as Market Place (above) and then 'Opps' the user realizes it should be Pizza Hut so they click on the cell... Offset is fixed, Pizza Hut appropriately turns blue, but Market Place is still blue... How can we get it such that clicking on Pizza Hut will cause Market Place (or any others) to go back to default non-filled?

Thanks again!

 

[My Aswer Is This]

Well things like this can become difficult. And the if this or that can possibly run on and on.

So, like if user double clicks on any cell in column 3 row greater than 21
the cell turns blue and so value in another cell is CF Or BB or whatever.
Then I guess you want that value removed also?

You can always click on the cell and then on Ribbon and change the color back to nothing and click on the cell and remove the value.

I started using Spreadsheets in the year 1985.
Users were always doing things to mess up my sheets I made for them.
So, I always made backups to my Material and stored them offsite so if users deleted all my formulas or such, I always had recent backups. Users would enter data in my cells which I had formulas and if you enter "Hello" in a cell with a formula the formula was now erased and the value "Hello" was now in the cell. Now users would pullout their hand-held calculators to get the value they needed and enter that value in the cell.

Hey, it kept me employed fixing things they messed up.

So how about we have the script look at column 1
And if we double click on column 1 row greater then 21
A Warning pops up and says are you sure you want to clear everything on the row.

If users enters "Yes" that entire row is cleared of all data and cell coloring.
Would that work?

 

[usnapoleon]

Well things like this can become difficult. And the if this or that can possibly run on and on.

So, like if user double clicks on any cell in column 3 row greater than 21
the cell turns blue and so value in another cell is CF Or BB or whatever.
Then I guess you want that value removed also?

You can always click on the cell and then on Ribbon and change the color back to nothing and click on the cell and remove the value.

I started using Spreadsheets in the year 1985.
Users were always doing things to mess up my sheets I made for them.
So, I always made backups to my Material and stored them offsite so if users deleted all my formulas or such, I always had recent backups. Users would enter data in my cells which I had formulas and if you enter "Hello" in a cell with a formula the formula was now erased and the value "Hello" was now in the cell. Now users would pullout their hand-held calculators to get the value they needed and enter that value in the cell.

Hey, it kept me employed fixing things they messed up.

So how about we have the script look at column 1
And if we double click on column 1 row greater then 21
A Warning pops up and says are you sure you want to clear everything on the row.

If users enters "Yes" that entire row is cleared of all data and cell coloring.
Would that work?

Interesting idea. Almost like having a clear button without the actual button

Let me take a twist at that.
Let me add a new column on the far Left. So essentially all columns shift to the right. Column 1 becomes column 2, etc. The new column 1 would be a "Clear" column - instead of typing Yes, let's just do a double click on column 1 and it will clear the row. But we'll have to limit the range of cells it clears. Further along the rows I have some formulas. I'll share them now with you:

WRSD Voucher Template mess - Copy.xlsm
	P	Q	R	S	T	U	V
20	ACT		Squirrel	Identified		this is purely to populate the Variance tab
21	Activities	Type	AMOUNT	Variance	Notes on Variances	Department	Date
22		FV				Pizza Hut
23		ACT				Theater
24
25
1

Cell Formulas
Range	Formula
U22:U25	U22	=IFERROR(INDEX($K$3:$K$12,MATCH($K22,$M$3:$M$12,0)),"")
V22:V25	V22	=IF(S22<>0,C$4,"")

Thoughts?

 

[My Aswer Is This]

I used the enter "Yes" which will help keep someone from double clicking on a cell in column 1 accidently which would then clear that row.
You seem to keep wanting to prevent someone from doing something accidently. But if you do not want this then that's OK.
I'm never really good with large formulas so cannot comment on your formulas. I'm good at Vba but not so much with large formulas.
If you do not want the entire row cleared, then you would have to tell me what cells on the row cleared.

You said:
"Instead of typing Yes"

My suggestion was:
You would double click on any cell in column 1 and a message box would popup asking are you sure. If you entered yes in the Inputbox then clicked OK then that row would be cleared. You would not enter "Yes" in the cell.

 

[My Aswer Is This]

Or a message box pops up with two buttons to click on.
if you choose Yes something happens or if you choose No something else happens

Like if you click yes, the row is cleared or if you click No nothing happens

 

[My Aswer Is This]

You said:
Let me add a new column on the far Left. So essentially all columns shift to the right. Column 1 becomes column 2

That is incorrect. Column 1 is always the column to the far left.
You would have to change the code.

Case 3: Target.Offset(, 8).Value = "CG": Target.Interior.Color = vbBlue
            Case 4: Target.Offset(, 7).Value = "MP": Target.Interior.Color = vbBlue
            Case 5: Target.Offset(, 6).Value = "PH": Target.Interior.Color = vbBlue

case 1 means column 1
Case 2 means column 2

etc,etc

 

[usnapoleon]

You said:
Let me add a new column on the far Left. So essentially all columns shift to the right. Column 1 becomes column 2

That is incorrect. Column 1 is always the column to the far left.
You would have to change the code.

Case 3: Target.Offset(, 8).Value = "CG": Target.Interior.Color = vbBlue
            Case 4: Target.Offset(, 7).Value = "MP": Target.Interior.Color = vbBlue
            Case 5: Target.Offset(, 6).Value = "PH": Target.Interior.Color = vbBlue

case 1 means column 1
Case 2 means column 2

etc,etc

I understand that column 1 is always to the left, I simply meant that everything shifts over. So what is column 1 will then be in column 2, etc etc.

I already modified the code to account for the shift:

    If Target.Row > 21 Then
        Select Case Target.Column
            'Case 1: Range(case
            Case 4: Target.Offset(, 8).Value = "CG": Target.Interior.Color = vbBlue
            Case 5: Target.Offset(, 7).Value = "MP": Target.Interior.Color = vbBlue
            Case 6: Target.Offset(, 6).Value = "PH": Target.Interior.Color = vbBlue
            Case 7: Target.Offset(, 5).Value = "BS": Target.Interior.Color = vbBlue
            Case 8: Target.Offset(, 4).Value = "MS": Target.Interior.Color = vbBlue
            Case 9: Target.Offset(, 3).Value = "SH": Target.Interior.Color = vbBlue
            Case 10: Target.Offset(, 2).Value = "TH": Target.Interior.Color = vbBlue
            Case 11: Target.Offset(, 1).Value = "ACT": Target.Interior.Color = vbBlue
           
            Case 13: Target.Offset(, 5).Value = "RW": Target.Interior.Color = vbBlue
            Case 14: Target.Offset(, 4).Value = "FV": Target.Interior.Color = vbBlue
            Case 15: Target.Offset(, 3).Value = "SB": Target.Interior.Color = vbBlue
            Case 16: Target.Offset(, 2).Value = "TH": Target.Interior.Color = vbBlue
            Case 17: Target.Offset(, 1).Value = "ACT": Target.Interior.Color = vbBlue
       
        End Select
    End If

I was trying to play with the code to see if I could write the code for column 1. I was going to add a 'clearcontents' but I didnt know how to define it at the top and I didnt know how to do a range of cells.

The reason I didnt want to utilize the existing column 1 is because that is a user field that is typed into already.
I get what you're saying with an accidental double-click and then "opps, we cleared the row by mistake!"... so your reasoning makes perfect sense. Going with what you were saying, they would type YES into column 1, and it would clear itself + the desired columns, right?
By the way, for the range of cells we'd clear, it would be columns 1-21

 

[Akuini]

@My Aswer Is This
I like your approach to deal with this problem, I think it's efficient & user-friendly.
If you don't mind, I'd like to try to amend your code to address the 2 type mistakes that the OP mentioned earlier.

@usnapoleon
If you interested, could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

 

[My Aswer Is This]

You said:
The reason I didnt want to utilize the existing column 1 is because that is a user field that is typed into already.
I get what you're saying with an accidental double-click and then "opps, we cleared the row by mistake!"... so your reasoning makes perfect sense. Going with what you were saying, they would type YES into column 1, and it would clear itself + the desired columns, right?
By the way, for the range of cells we'd clear, it would be columns 1-21

I never said: "they would type YES into column"
I said they would double click on column 1
Then a MsgBox would Popup and give you a choice of clicking a button which would say yes or no. If you clicked yes, the script would run if you clicked No the script would not run