PDA

View Full Version : Vba Code Error



srivardhan09
12-02-2009, 08:57 AM
Dear All,

Can you please help in solving this code


Private Sub CommandButton1_Click()


If CheckBox1.Value = True Then
Range("a1:b7").AutoFilter Field:=1, Criteria1:="A"

GoTo jump1
Else
If CheckBox1.Value = False Then
GoTo jump1
jump1:
If CheckBox2.Value = True Then
Union(Selection, Range("a1:b7")).AutoFilter Field:=1, Criteria1:="B"

GoTo jump2
Else
If CheckBox2.Value = False Then
GoTo jump2
jump2:
If CheckBox3.Value = True Then
Union(Selection, Range("a1:b7")).AutoFilter Field:=1, Criteria1:="C"


End If
End If
End If
End If
End If
End Sub


Regards,
Srivardhan

Edit: Please use the VBA button instead of the quote button to format your code.

RolfJ
12-02-2009, 09:29 AM
Gee! It sure would help if you were to tell us what you are trying to accomplish with this code... :doh:

GTO
12-02-2009, 09:53 AM
Greetings Srivardhan,

Say, I see it is your first post and that you just joined. Let me say 'Welcome' and a friendly 'Howdy' :hi: from Arizona. You will 'meet' some nice folks here and get great help:)

As you your current problem, I'd certainly agree with Rolf in that it is unclear. I'm just getting a teeny bit better w/filtering, but I tried making a simple example sheet, and although I doubt the GoTo's are the optimum, nothing "blew up" or anything.

You may wish to attach an example workbook, which you can do by:

Press the Go Advanced button under the Quick Reply box; this will take you to a new window.
Below the larger reply box, press the Manage Attachments button.Hope that helps,

Mark

Bob Phillips
12-02-2009, 10:07 AM
Doesn't this code (and the next such example) seem a tad pointless to you?



If CheckBox1.Value = False Then
Goto jump1
jump1:

srivardhan09
12-02-2009, 10:16 AM
Dear All,

Nice to meet you all.....

The Problem what i am facing is

've got a sheet with some 40 or so check boxes representing project requirements. I also have a list of ~200 text filled cells, each of which correspond to necessary actions. What I'm trying to determine is the code to allow me to select certain cells from the list based on the check box(s) selected. Different project requirements might share the same options.

For example

selecting checkbox1 might filter only "A"
selecting checkbox2 might select cells "B"

and selecting both (checkbox1 & checkbox2) would filter for "A" & "B"

Any ideas would be appreciated.


Please help me get out of this problem

srivardhan09
12-02-2009, 10:19 AM
Dear All,

Below is the code that works for selecting the records using check boxes.

Private Sub CommandButton1_Click()
If CheckBox1.Value = True Then
Range("a1:b5").Select

GoTo jump1
Else
If CheckBoxML.Value = False Then
GoTo jump1
jump1:
If CheckBox2.Value = True Then
Union(Selection, Range("a6:b10")).Select
GoTo jump2
Else
If CheckBox2.Value = False Then
GoTo jump2
jump2:
If CheckBox3.Value = True Then
Union(Selection, Range("a10:b15")).Select


End If
End If
End If
End If
End If

If i am using autofilter it is not working

Private Sub CommandButton1_Click()


If CheckBox1.Value = True Then
Range("a1:b7").AutoFilter Field:=1, Criteria1:="A"

GoTo jump1
Else
If CheckBox1.Value = False Then
GoTo jump1
jump1:
If CheckBox2.Value = True Then
Union(Selection, Range("a1:b7")).AutoFilter Field:=1, Criteria1:="B"

GoTo jump2
Else
If CheckBox2.Value = False Then
GoTo jump2
jump2:
If CheckBox3.Value = True Then
Union(Selection, Range("a1:b7")).AutoFilter Field:=1, Criteria1:="C"


End If
End If
End If
End If
End If
End Sub

Regards,
Srivardhan

srivardhan09
12-02-2009, 10:29 AM
Dear Xld,

What you said is correct... i will remove that else statement and increase the performance...

Thank You for your valid suggestion....

Regards,
Srivardhan

Bob Phillips
12-02-2009, 10:54 AM
The proble is that autofilter canot handle 40 criteria, so it cannot be used with 40 checkboxes.

Is Advanced filter an option, as we could build a criteria list for that.

srivardhan09
12-02-2009, 10:56 AM
Is there any other option for selecting one or more check boxes based on condition.

Bob Phillips
12-02-2009, 12:04 PM
It isn't the selecting of the checkboxes, it is using them.

srivardhan09
12-02-2009, 12:07 PM
Yes... can you please help regrading that code i am trying from past 4 days... totally vexed up!!!: pray2: