Checkbox in Userform

lukael · #1 02-18-2014, 03:26 AM

Hello, I want to make this code working on my Userform1, which has checkbox in It. Userform1 is activated with macro1(Userform1.Show) on worksheet (button). Code works great wih no Userfom :

Code:

Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim blnValue As Boolean 
    Dim shpCB As Shape 
    If Target.Count = 1 Then 
        If Not Intersect(Target, Range("C4,C6")) Is Nothing Then 
            blnValue = WorksheetFunction.CountA(Range("C4,C6")) = 2 
            For Each shpCB In ActiveSheet.Shapes 
                If shpCB.Name Like "Check*" Then 
                    shpCB.ControlFormat.Value = blnValue * -1 
                End If 
            Next shpCB 
        End If 
    End If 
End Sub

macropod · #2 02-18-2014, 04:55 AM

I don't see why you'd want to add that code to a userform, since it already runs automatically when the worksheet is changed. Still, if that's all you want to do, simply call it from your userform, passing whatever range you want to use as the target.

lukael · #3 02-18-2014, 05:03 AM

Quote:

Originally Posted by macropod

I don't see why you'd want to add that code to a userform, since it already runs automatically when the worksheet is changed. Still, if that's all you want to do, simply call it from your userform, passing whatever range you want to use as the target.

I have post this code in Initialize and Activate event of Userfom, but nothing happens. I get Error 424-Object required !

My guess, this line is the problem :

Code:

If Target.Count = 1 Then

macropod · #4 02-18-2014, 05:06 AM

Have you specified the target range?

lukael · #5 02-18-2014, 05:07 AM

Quote:

Originally Posted by macropod

Have you specified the target range?

this is from the code above, range:

Code:

If Target.Count = 1 Then 
        If Not Intersect(Target, Range("C4,C6")) Is Nothing Then 
            blnValue = WorksheetFunction.CountA(Range("C4,C6")) = 2

It must tick checkbox ONLY IF both cells (C4,C6) are not blank - which is thier default value !

lukael · #6 02-18-2014, 05:20 AM

I have found solution, thanks anyway !!!

Solution :

http://www.mrexcel.com/forum/excel-q...ml#post3720908

or in code:

Code:

Option Explicit

Private Sub UserForm_Initialize()
Me.CheckBox1 = application.WorksheetFunction.CountA(Sheet1.Range("E4,E6")) = 2
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Userform Code not quite right - help please	vbanovice	Word VBA	1	09-29-2013 09:20 PM
Help with a drop down in userform and variables	leemoreau	Word VBA	1	09-14-2013 06:01 AM
How to use the SaveAsDialog after UserForm is closed	drod	Word VBA	0	03-03-2013 08:06 PM
Outlook userform validation help	aiwnjoo	Outlook	0	12-08-2010 12:57 AM
Checkbox on Userform result in Text in Word	Dolfie_twee	Word VBA	1	06-22-2010 07:54 AM