Checkbox Code Help please ,,,

[slimimi]

Hi there. thanks in advance for looking in

I have a userform with 6 checkboxes.
Users need to select 2 - if they try to select 3 (msgbox) but how can i make the msgbox appear when a user attempts to tick a 3rd box rather than on a command click?

Moving on : Once they select 2 checkboxes, i need to determine which 2 boxes were checked out of the 6.

This was the old code i had - but i am not sure how to modify it.
Any help will be really appreciated:


'''''''' Now there will be 6 checkboxes and i am not sure how to modify the code below so that message appears only if user tries to tick 3rd box

Private Sub cmdChartOK_Click()

If CheckBox4.Value = 0 Then
MsgBox "You can only select 2 charts per trade", vbExclamation
Exit Sub
End If

'''''''' The rest of the code

Dim wb As Workbook, ws As Worksheet
Dim fso As Object, f As Object
Dim dfound As Boolean, hfound As Boolean

On Error GoTo ErrHandler

Set fso = CreateObject("scripting.filesystemobject")
Set wb = ActiveWorkbook
Set ws = ActiveSheet
dfound = False
hfound = False

'''''''' The 6 checkboxes are named as follows :M1, M5, M30, H1, H4, D1I now need to modify the code below to determine which 2 out of the 6 boxes were checked instead of just simply adding h1 to the end of the name (as marked in red below) :

For Each f In fso.getfolder("C:\documents and settings\" & Environ("USERNAME") & "\desktop\Trade Logs\Chart Pictures").Files
If UCase(ws.Range("B" & ActiveCell.Row).Value & "h1") = UCase(Left(f.ShortName, InStr(f.ShortName, ".") - 1)) Then
ws.Range("i" & ActiveCell.Row).Hyperlinks.Add ws.Range("i" & ActiveCell.Row), f.Path, , , f.Name
hfound = True
Exit For
End If
Next f
If hfound = False Then
MsgBox "Requested Chart Not Found"
ws.Range("i" & ActiveCell.Row) = "No Chart"


ws.Range("i" & ActiveCell.Row).Hyperlinks.Delete

With ws.Range("i" & ActiveCell.Row)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 43
End With

With ws.Range("i" & ActiveCell.Row).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With ws.Range("i" & ActiveCell.Row).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
End If

Then i believe i will need to modify this code below for filling the listboxes but i cant get my head round how to do it :

    With ListBox1
        .Font.Bold = True
        .AddItem Sheets("Mini Forex").Range("B" & ActiveCell.Row).Value & "h1.gif"
        .Height = 20
    End With

'''''''' Right now its adding h1 to the end of the name from a cell on the sheet. But I would like to modify these codes so that the listbox is populated depending on what the user checked instead. IE, user ticks checkbox M5, so m5 gets added to the end of the name for populating Listbox1 - then for 2nd checkbox that user ticks, eg, D1, then d1 gets added to the end of the name for populating Listbox2.

With ListBox2
.Font.Bold = True
.AddItem Sheets("Mini Forex").Range("B" & ActiveCell.Row).Value & "d1.gif"
.Height = 20
End With

Thanks so so so much in advance if anyone can help me figure this out
Hope it all makes sense.

 

[slimimi]

Hi there

I think i would still like the userform to have 2 boxes (of some form - not sure whats best) which serves as some sort of confirmation to the user before they click OK. The filename that populates in this box is good because it also shows the user the correct name that they "should" have saved their chart pictures as.

Hope that makes sense.
All the best and thanks again.

 

[slimimi]

Hi there - i think it would still be cool to keep 2 boxes on the form which shows the username the whole filename which will be attached to their chart once they press ok. It serves as a reminder to

1) make sure they "have" saved their pictures and
2) to show them the correct filename they "should" have saved their pictures as.

Hope this makes sense.
Thanks again for all your help

 

[erik.van.geit]

I think i would still like the userform to have 2 boxes (of some form - not sure whats best) which serves as some sort of confirmation to the user before they click OK.

you can use labels to do this
(perhaps put in a frame)

add two labels to your userform
add some code to the classmodule

Option Explicit
 
Public WithEvents CheckBoxGroup As MSForms.CheckBox
 
Private Sub CheckBoxGroup_Click()
    If cnt > 2 Then
    CheckBoxGroup = False
    MsgBox "Max 2 can be selected", vbCritical, "STOP"
    Else
    putinlabel
    End If
End Sub
 
Function cnt()
Dim ctrl As MSForms.Control
 
    For Each ctrl In UserForm1.Controls
        If TypeName(ctrl) = "CheckBox" Then
        If ctrl = True Then cnt = cnt + 1
        End If
    Next ctrl
        
End Function
 
Sub putinlabel()
Dim i As Long
Dim ctrl As MSForms.Control
 
Application.ScreenUpdating = False
 
    With UserForm1
    .Label1.Caption = vbNullString
    .Label2.Caption = vbNullString
        For Each ctrl In .Controls
            If TypeName(ctrl) = "CheckBox" Then
                If ctrl = True Then
                i = i + 1
                .Controls("Label" & i) = ctrl.Caption
                End If
            End If
        Next ctrl
    End With
 
Application.ScreenUpdating = True
 
End Sub

You have now seen enough code to have a play yourself, I think. Feel free to come back if needed.

kind regards,
Erik

 

[slimimi]

Thank you so much.
Sorry to keep troubling you but i am getting a compile error on this line

ReDim Preserve chBoxes(i)

The error says : Variable not defined

This is under the Userform Initialize

Also can i please ask you - regarding the code below :
How would i modify the Red Parts on this to deal with this file search and hyperlink insert process now:

Dim wb As Workbook, ws As Worksheet
Dim fso As Object, f As Object
Dim dfound As Boolean, hfound As Boolean

On Error GoTo ErrHandler

Set fso = CreateObject("scripting.filesystemobject")
Set wb = ActiveWorkbook
Set ws = ActiveSheet
dfound = False
hfound = False

For Each f In fso.getfolder("C:\documents and settings\" & Environ("USERNAME") & "\desktop\Trade Logs\Chart Pictures").Files
If UCase(ws.Range("B" & ActiveCell.Row).Value & "h1") = UCase(Left(f.ShortName, InStr(f.ShortName, ".") - 1)) Then
ws.Range("i" & ActiveCell.Row).Hyperlinks.Add ws.Range("i" & ActiveCell.Row), f.Path, , , f.Name
hfound = True
Exit For
End If
Next f
If hfound = False Then
MsgBox "Hour Chart Not Found"
ws.Range(Z"i" & ActiveCell.Row) = "No Chart"


ws.Range("i" & ActiveCell.Row).Hyperlinks.Delete





With ws.Range("i" & ActiveCell.Row)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 43
End With

With ws.Range("i" & ActiveCell.Row).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With ws.Range("i" & ActiveCell.Row).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
End If






For Each f In fso.getfolder("C:\documents and settings\" & Environ("USERNAME") & "\desktop\Trade Logs\Chart Pictures").Files
If UCase(ws.Range("B" & ActiveCell.Row).Value & "d1") = UCase(Left(f.ShortName, InStr(f.ShortName, ".") - 1)) Then
ws.Range("j" & ActiveCell.Row).Hyperlinks.Add ws.Range("j" & ActiveCell.Row), f.Path, , , f.Name
dfound = True
Exit For
End If








Next f
If dfound = False Then
MsgBox "DAY chart Not Found"
ws.Range("j" & ActiveCell.Row) = "No Chart"



ws.Range("j" & ActiveCell.Row).Hyperlinks.Delete


With ws.Range("j" & ActiveCell.Row)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 43
End With
With ws.Range("j" & ActiveCell.Row).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With ws.Range("j" & ActiveCell.Row).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
End If







Unload Me


Exit Sub

Thanks a MILLION in advance ))

 

[erik.van.geit]

you must have deleted a declaration line of the code

Anyway, when the error tells you "variable not defined", then all you need to do is ...
"DEFINE IT"

Dim chBoxes ... see my code

EDIT: you lost me on the second question?
is it related??
or do you mean that you are searching for a way to replace "h1" by the Label caption?

 

[slimimi]

Thats wonderful - i tried in new workbook and works great - thanksssssssssssss.
Just need to figure out how to paste it into my template

Regarding the Label.
How can i make the label comprise of the following though:

Column B & ActiveCell Row & label caption & ".gif"

Previously i was using

With ListBox1
.AddItem Sheets("Mini Forex").Range("B" & ActiveCell.Row).Value & "h1.gif"

Regarding the 2nd question:
The label caption will now be the actual filename (as above) so i guess i need to change the following - would this work?

If UCase(ws.Range(label1.caption) = UCase(Left(f.ShortName, InStr(f.ShortName, ".") - 1)) Then
ws.Range("i" & ActiveCell.Row).Hyperlinks.Add ws.Range("i" & ActiveCell.Row), f.Path, , , f.Name
hfound = True

Also, if you dont mind please, i have another checkbox on this form which is not part of what we were doing earlier - how can i exclude this from the ctrl counts?

Thanks a million in advance...

 

[erik.van.geit]

this should work

UCase(ws.Range(UserForm1.Label1.Caption)

but the UserForm must still be in memory

else you will need to store the caption somewhere

to exclude the checkbox, check for the name of the checkboxes before adding them to the group

        If TypeName(ctrl) = "CheckBox" Then
        i = i + 1
        ReDim Preserve chBoxes(i)
        Set chBoxes(i).CheckBoxGroup = ctrl
        End If

insert something like
If ctrl.Name <> "ThisOne" Then ...

    If TypeName(ctrl) = "CheckBox" Then
        If ctrl.Name <> "ThisOne" Then
        i = i + 1
        ReDim Preserve chBoxes(i)
        Set chBoxes(i).CheckBoxGroup = ctrl
        End If
    End If

typed off hand and untested, but should be close