Checkbox Code Help please ,,,

slimimi · Jun 20, 2008

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 :

Rich (BB code):

    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.

erik.van.geit · Jun 20, 2008

Hi,

Try this.
Insert a classmodule and name it "Class1" (if it is not already named that way)
paste this code

Code:

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"
    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

create a userform named UserForm1 with some checkboxes and a button named CommandButton1

Code:

Option Explicit
 
Dim chBoxes() As New Class1
 
Private Sub CommandButton1_Click()
list_checked
Unload Me
End Sub
 
Private Sub UserForm_Initialize()
Dim i As Integer
Dim ctrl As Control
 
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CheckBox" Then
        i = i + 1
        ReDim Preserve chBoxes(i)
        Set chBoxes(i).CheckBoxGroup = ctrl
        End If
    Next ctrl
 
End Sub
 
Sub list_checked()
Dim msg As String
Dim ctrl As MSForms.Control
 
    For Each ctrl In UserForm1.Controls
        If TypeName(ctrl) = "CheckBox" Then
        If ctrl = True Then msg = msg & vbNewLine & ctrl.Name
        End If
    Next ctrl
 
MsgBox "These boxes are checked:" & msg, vbOKOnly, "CHECKED"
 
End Sub

Now have a play with this.

kind regards,
Erik

EDIT: if you want to use the function "cnt" for other purposes, you can will it in a normal module

slimimi · Jun 21, 2008

Thats fantastic thank you.
But how do i now get that to populate the 2 listboxes with each checkbox ticked now?
Would i put his instead?

I tried adding the following to your code but no joy:

Private Sub UserForm_Initialize()
Dim i As Integer
Dim ctrl As Control

For Each ctrl In Me.Controls
If TypeName(ctrl) = "CheckBox" Then
i = i + 1
ReDim Preserve chBoxes(i)
Set chBoxes(i).CheckBoxGroup = ctrl
End If
Next ctrl

With ListBox1
.Font.Bold = True
.AddItem Sheets("sheeet1").Range("B" & ActiveCell.Row).Value & ctrl.Name
.Height = 20
End With

End Sub

I need to populate 2 listboxes with the name of each checkbox ticked (which will only by 2 because we are only allowing the user to tick 2 boxes). Then for each ctrl - i need the 2 listboxes to be populated accordingly.

I know i could just put them in 1 list but i need to seperate them because, later on, once the user clicks the command button, there is code that will look for files (based on what checkboxes the user ticks). Hope this makes sense.#

Thanks for all your help.

slimimi · Jun 21, 2008

just to make things easier - this procedure would be ideal :

User ticks 2 Checkboxes
Listbox1 is populated with "B" + Active Row Selection + Name of 1st Checkbox ticked
Listbox2 is populated with "B" + Active Row Selection + Name of 2nd Checkbox ticked
User clicks Command Button
Look for & find file1 based on data in Listbox1
Look for & find file2 based on data in Listbox2

Hope this helps.

erik.van.geit · Jun 21, 2008

and why do you need the listboxes?
can you explain a bit?

slimimi · Jun 21, 2008

Ok - basically i have created a trade log so users can analyze their trades with me.

Part of that analysis involves saving 2 Pictures for the SAME trade BUT different timeframes, Eg, 30minutes chart (M1), Day Chart (D1). Previously i set it so that user HAD to save an Hour Chart and a Day Chart (H1 and D1). But now i need to expand this more.

I created a custom Toolbar command button on my sheet.
This command button is called "Get Chart Pictures".

This is what the user done before in the past
The user has to enter a Ticket Number into Column B (example: 113355)
Then user clicks on GET CHART PICTURES

If there is NO entry in Column B - user gets prompted with message"You must enter a Ticket Number First to proceed"

If there is an entry in Column B - then the following happens:

Show Userform called 'frmChartPics'

On this userform - there are 2 listboxes and a Command Button

Listbox1 is populated by taking the Active Row Selection+Column B+"h1.gif", eg: 113355h1.gif

Listbox2 is populated by taking the Active Row Selection+Column B+"d1.gif",eg: 113355d1.gif

I done this so that users can confirm they have previously saved their Chart Pictures with the correct names. It also serves as a mental reminder if they have forgotten to actually SAVE any Chart Pictures.

Once user confirms - then the following happens:

A hyperlink to the Hour Chart (h1) is inserted into Column I
A hyperlink to the Day Chart (d1) is inserted into Column J

If there are no charts by the name 113355h1 or 113355d1 - then the words 'No Chart" will appear in Columns I and J.

Now i need to expand this all with Variables i guess.
The userform will now have 6 checkboxes instead so that users are NOT limited to just analyzing or Saving an Hour or Day Chart picture. The userform will now have a choice whereby user can pick any 2 timeframes out of 6, eg, M1, M5, M30, H1, H4 or D1.

This is what i would like to happen now when user clicks on 'Get Chart Pictures'
Userform shows
User checks 2 out of 6 checkboxes

Listbox1 is populated with Row Selection+Column B+1st checkbox name that user ticks+".gif"

Listbox2 is populated with Row Selection+Column B+2nd checkbox name that user ticks+".gif"

Once user confirms choice by clicking OK command button - i would like the following to happen.

Use Listbox1 as Filename to Search for Chart 1 - which will be placed in Column I

Use Listboc2 as Filename to Search for Chart 2 - which will be placed in Column J

------------------------

Hope this all makes sense now.

The previous code i had was great and worked fine BUT users were limited to saving an H1 and a D1 chart only. This time i want them to have the choice so i believe that the code i posted earlier in this thread needs to now be modified to do the following:

Don't allow user to check more than 2 boxes (DONE - thank you)
See what 2 checkboxes user has selected (DONE - thank you)
Populate Listbox1 with 1st Checkbox that user ticks (not done yet)
Populate Listbox2 with 2nd Checkbox that user ticks (not done yet)

Modify search file codes accordingly (i thought the easiest way would be to just use Listbox1 value to search for 1st Chart Picture and use Listbox2 value to search for 2nd Chart Pictue.

Well - i hope it makes more sense now - sorry for the long winded post - i just find it very hard to write out the problem clearly,

Thanks in advance.

erik.van.geit · Jun 22, 2008

a long explanation ...
seems that was not needed

in the end I'm still not sure if you really need listboxes: to me it seems you are only going to put 1 item in them, so a "list" makes no sense to me
I do not even see the need of putting the captions of the checkboxes in a control at all, you can just use them as is.

else I'm missing something?

slimimi · Jun 22, 2008

Sorry about the long explanation

Ok - you are right - it makes no sense to use listboxes. I just thought that i would not be able to populate Textboxes in the same way (which is why i went for lists). How different would the code be to populate a textbox instead?

You are right about the checkboxes too - no need to put captions.
I just thought the names would be a good way to identify which checkbox was for which time interval, ie, m1, m5, m30, etc, etc...

Does that help?

Would you be able to help me further to modify my initial code please so i can get this to work., I been trying all weekend to find a way and i am just going round and round in circles (haha) because of my VERY limited knowledge with VB.

Much appreciated if you can.

Look forward to hearing from you.
Best Regards.

slimimi · Jun 22, 2008

please let me know if you need any more information.
Cheers

erik.van.geit · Jun 22, 2008

OK, we might get close to "break through" not

presivously you wrote

User clicks Command Button
Look for & find file1 based on data in Listbox1
Look for & find file2 based on data in Listbox2

would that be now
Look for & find file1 based on "true" checkboxes?

or do you still want to use additional controls (textboxes, or better labels)

Checkbox Code Help please ,,,

slimimi

Well-known Member

Excel Facts

erik.van.geit

MrExcel MVP

slimimi

Well-known Member

slimimi

Well-known Member

erik.van.geit

MrExcel MVP

slimimi

Well-known Member

erik.van.geit

MrExcel MVP

slimimi

Well-known Member

slimimi

Well-known Member

erik.van.geit

MrExcel MVP

Similar threads

Forum statistics

Share this page

Checkbox Code Help please ,,,

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock