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!

 

[usnapoleon]

If you want to delete the X
you could use something like this:

Double click on any of these columns and the X will be removed:
In this script modify to your needs:
3, 5, 7, 8, 14, 18
The script will delete the X from and row in columns 3, 5, 7, 8, 14, 18

So I'm thinking all is well now and you have this working the way you want is that correct?
I did not understand this:
You said:
There is one thing we didnt touch upon, the visual aspect of it. I get there is an invisible 'x' there based on the double-click. Is there anything we can do visually to show the cell selection?
what is an invisible X?



Now I could add to this, and the script could delete the X if you want.
It would popup an input box and would ask "Enter X or Delete X
Would you like something like that added.

Sorry but I am not understanding the modification needed to the script to remove the X when it's double-clicked. I was looking the script over and it just wasnt coming to me.

On the part I wasnt making clear, about the invisible X...
When we double-click a cell, it follows the script and inputs the target value of X into the cell, which we can see here:

So that X is there, but it doesnt 'display' it. Visually among all the columns nothing shows up to indicate 'this is the chosen cell' other than the display 'PH' in column 8. Is there anything we can do on this? Maybe change the cell color or something?

If we are able to do color changes, I do see 1 challenge with this - what happens if we double-click one cell, then realize we clicked the wrong one? When we double click the correct cell, the first mistakenly-clicked cell will need to revert back to the original color. (I get my spreadsheet does alternating colors between rows, so I will need to change that so it's all 1 color).
Maybe clicking 1 cell resets the other cells in that particular group?

Just throwing thoughts out there! Thank you!

 

[My Aswer Is This]

You said:
sorry but I am not understanding the modification needed to the script to remove the X when it's double-clicked. I was looking the script over and it just wasnt coming to me.

I asked did you want this:
So, if we want a script to delete the X if double clicked on the x I can add that.
Do you want to just delete the x, or do you want the entire row the x is in cleared?
As far as the other part about hidden x let's just take this one step at a time.

When we get into these what if this or that happens those type things can go on and on.
And changing a cell color and then wanting to change it back to the original would be difficult. Where would the script store the previous color if needed?

 

[usnapoleon]

You said:
sorry but I am not understanding the modification needed to the script to remove the X when it's double-clicked. I was looking the script over and it just wasnt coming to me.

I asked did you want this:
So, if we want a script to delete the X if double clicked on the x I can add that.
Do you want to just delete the x, or do you want the entire row the x is in cleared?
As far as the other part about hidden x let's just take this one step at a time.

When we get into these what if this or that happens those type things can go on and on.
And changing a cell color and then wanting to change it back to the original would be difficult. Where would the script store the previous color if needed?

Hello!
For clearing X, I am looking at 2 things desired, if possible. I'll add my previous snip as a visual reference:

Scenario 1:
I double click E22... I want C22, D22, F22, G22 to be cleared. I understand that H22 (column 8) is only going to represent the 'last cell clicked' but I want to prevent people getting confused looking at columns 3-7 and seeing an X in the formula bar in several of those other columns/cells because we double clicked on a few before choosing E22.

Scenario 2:
I double click E22... then decide I don't need to input anything in that row. If I double click E22 again, can we get it to remove the X?

So we're not looking to delete any rows from existence. We're not looking to clear out the entire row, especially because we have a 2nd group there (the 'Voucher Type' group). We're just looking to have specific contents cleared based on the 2 above scenarios.



For the color... I was trying to find information on this online, and I didnt have success... but I was trying! Most examples were employing modules rather than the VBA code on the worksheet. I was thinking we could do something based on that 'X' value. if value is X then change the color... I saw an example for yellow as RGB(255, 255, 0). As far as remembering the previous color, what about an 'else' statement for when values are not X?
I suck at this so bad!! But maybe my rambling is helping you envision it!

Thank you again!

 

[usnapoleon]

Oh, I successfully did the script for the 2nd group "Voucher Types". Because of what you showed me, that part was really simple. I even added the additional columns into the mix for both groups. Thank you for that!! Amazing!!

 

[My Aswer Is This]

You said:
I double click E22... then decide I don't need to input anything in that row. If I double click E22 again, can we get it to remove the X?

I assume it will not always be row 22
So any time you double click on any cell in column E
You want a x entered if there is no x
if there is a x you want the x removed and

Then you said:
"if value is X then change the color"
change the color to what color?
I can tell the script to remove the x and then set the cell color to Green or red or some other color

You also said:
Most examples were employing modules rather than the VBA code.

Module code is Vba code

If we put the code in a module you will need to click a button to run the script
We are using Vba code but having it run when you double click on some cell

So, you only mentioned column E if there are more columns I need to know that.
Say something like column B C E G H K or what other

 

[usnapoleon]

Hi!

You said:
I double click E22... then decide I don't need to input anything in that row. If I double click E22 again, can we get it to remove the X?

I assume it will not always be row 22
So any time you double click on any cell in column E
You want a x entered if there is no x
if there is a x you want the x removed and

Then you said:
"if value is X then change the color"
change the color to what color?
I can tell the script to remove the x and then set the cell color to Green or red or some other color

You also said:
Most examples were employing modules rather than the VBA code.

Module code is Vba code

If we put the code in a module you will need to click a button to run the script
We are using Vba code but having it run when you double click on some cell

So, you only mentioned column E if there are more columns I need to know that.
Say something like column B C E G H K or what other

First part you mentioned... yes! Enter X if there is no X, remove X if there already is. Absolutely, and correct about the row, I was just using row 22 as an example from the snip.

Second part... again yes, you are on fire! I think a blue would look good. It's cool how your mind works. You instantly thought 'lets do a color instead of X' and that didnt even occur to me that we could swap the intended target value from a letter to a color.

And sorry... when I said people were using modules instead of the vba code, I meant to say instead of the worksheet. Sorry for the error in verbiage. Not that I was looking for code in a module, I was just pointing out what I observed when surfing the net. You can completely forget I mentioned it

Let me show you the updated/current sheet, and the code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Application.ScreenUpdating = False
 Application.EnableEvents = False

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

Application.ScreenUpdating = True
Application.EnableEvents = True


End Sub

 

[My Aswer Is This]

Try this it makes the cells Blue not a X

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

 

[My Aswer Is This]

If you do not like the blue color

Try something like this

Dim cc As String
cc = Range("A1").Interior.Color
ActiveCell.Interior.Color = cc

Then the script would look something like:
Target.interior.color=cc
And then change the script if you think you can.
Or let me know what cell in the sheet have you setup with the color you like.
and I will modify the script

 

[usnapoleon]

If you do not like the blue color

Try something like this

Dim cc As String
cc = Range("A1").Interior.Color
ActiveCell.Interior.Color = cc

Then the script would look something like:
Target.interior.color=cc
And then change the script if you think you can.
Or let me know what cell in the sheet have you setup with the color you like.
and I will modify the script

Hello! Sorry for the delay, I was out all day and it feels good to be back in front of the computer!

The blue code worked, I liked the color!!

"Target.Interior.Color" ... none of my online research took me in that direction, but as I compare it to some of what I saw, I can see I overlooked things. 'Interior.Color' is the key point. I saw one person do xCell1.Interior.Color and now that I relook at things I understand they had defined xCell1 in the same way we defined Target. Don't mind me, just typing out-loud as I try to piece my understanding of this more and more with following your code.

Here is a snip of the results:

I took off the previous alternating pinkish colors for the rows so we're looking at the default 'no-fill' for the cells, and the selected ones have the blue color. Awesome!

Just FYI, the message box popup didnt happen. Not complaining though, I bet my team would get really annoyed with that since they are going to be working on the spreadsheet daily!

It seems all we need now are 2 things:

1) A way to prevent the multiple blue-boxes from occurring in a single group on the same row, as we see with columns 4 & 5 on row 22, and columns 6 & 9 on row 23.

2) A way to un-select... un-blue-box... a cell that got selected in error.


Again, sorry I havent been around all day to look at this!

 

[My Aswer Is This]

You said 1) A way to prevent the multiple blue-boxes from occurring in a single group on the same row, as we see with columns 4 & 5 on row 22, and columns 6 & 9 on row 23.

I hope you understand offset in my script.
If you are doubling clicking on column B and you have Offset 3 then the blue color is entered in column E

I thought I had it working correctly but maybe not. I will go back and look at it again.

Post your script here and I will look at it.