PDA

View Full Version : Adding Continuous Check Boxes



IcePirates
01-12-2009, 07:38 AM
Hello,

My question is simple, how do I add a check-box to every line in my excel sheet

If you look at my attachment in the 'File Number' column sometimes Company1 sends more than just one file, in this case Company1 is sending two files (as you can see on the sheet)

I want users to have check boxes beside every file number in that column that allow them to select which file numbers they want to include when creating the word document. (This sheet allows you to create a word document once you have filled in cells A, B, C

If you view my attachment -> Any help is greatly appreciated!

Bob Phillips
01-12-2009, 07:50 AM
A different approach

This works by double-clicking in a cell, and toggles the check-mark.



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Range(WS_RANGE)) Is Nothing Then
With Target
.Font.Name = "Marlett"
Select Case .Value
Case "": .Value = "a"
Case "a": .Value = ""
End Select
End With
End If
err_handler:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

IcePirates
01-12-2009, 08:32 AM
Hey,

Thanks for the suggestion,

My one question is this, in 'Sheet1' in the Visual Basic Editor, I already have the script for the command buttons and drop down, (this is the script)
Private Sub ComboBox1_Change()
End Sub
Private Sub CommandButton1_Click()
TransferData
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Target.Row = Me.CommandButton1.TopLeftCell.Row Then Exit Sub
Me.ComboBox1.Top = Range("D" & Target.Row).Top
Me.ComboBox1.Left = Range("D" & Target.Row).Left
Me.CommandButton1.Top = Range("E" & Target.Row).Top
Me.CommandButton1.Left = Range("E" & Target.Row).Left

End Sub



How would I implement the code you provided into that ^?
(Hopefully you dont mind me asking, I just dont want to mess anything up)

Thanks!

Bob Phillips
01-12-2009, 08:46 AM
It would just be anither procedure in the same code module.

lucas
01-12-2009, 08:52 AM
As long as you don't already have an on doubleclick event in that module it won't matter.

double click in column H for a checkmark

IcePirates
01-12-2009, 09:04 AM
Hey,

Ok, so my code in the sheet looks like this now:


Private Sub ComboBox1_Change()
End Sub
Private Sub CommandButton1_Click()
TransferData
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Target.Row = Me.CommandButton1.TopLeftCell.Row Then Exit Sub
Me.ComboBox1.Top = Range("D" & Target.Row).Top
Me.ComboBox1.Left = Range("D" & Target.Row).Left
Me.CommandButton1.Top = Range("E" & Target.Row).Top
Me.CommandButton1.Left = Range("E" & Target.Row).Left

End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "B2:B6" '<=== change to suit

On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Range(WS_RANGE)) Is Nothing Then
With Target
.Font.Name = "Marlett"
Select Case .Value
Case "": .Value = "a"
Case "a": .Value = ""
End Select
End With
End If
err_handler:
Application.EnableEvents = True
End Sub


However, if you view my attached example, it allows you to select more than 1 row now, but try clicking in B1 twice, it just displays a bunch of characters - then when you create the word document it doesnt include the rows you selected.

IcePirates
01-12-2009, 09:05 AM
Sorry, forgot to post example

lucas
01-12-2009, 09:25 AM
Do you want your checkmarks in column B? You chaned Bob's code to operate on column B when you double click it.

IcePirates
01-12-2009, 09:26 AM
Yep, I do want my check marks to be in column B, but if you look at the excel sheet example I provided, his code allows you to select the cell, but when you do, some weird characters show in the cell, not check boxes...

Do you see the same thing?

lucas
01-12-2009, 09:33 AM
Well, clear the contents of the column b and try again. It works fine if there is no text there.

IcePirates
01-12-2009, 09:36 AM
Well - there has to be text in the cells, because how else is the user going to know what file number to select if there is no text in the cells to show which file number their selecting..

Would this work better if I added a column and put the check boxes in a column, beside the file number? Do you think that'd help?

lucas
01-12-2009, 09:38 AM
Would this work better if I added a column and put the check boxes in a column, beside the file number? Do you think that'd help?

yes

IcePirates
01-12-2009, 09:43 AM
Ok, I did that, Ive attached another example, column "C" is where you double-click for to select the file...

But, there is still one small problem, when selecting more than one file number and creating the document, it doesn't show both file numbers I selected from the Excel sheet...Can you venture a guess?

Im not sure, but Im looking through the code now

IcePirates
01-12-2009, 10:13 AM
Ok, I got it to work, however the only thing now is -
When selecting more than one file number then creating the word document, the script doesn't pass all the file n umbers selected...Could this be a problem with Application.Intersect and not having the proper values defined?

Or what do you guys think?