Need help with CommandButton_Click

[jqzhang]

Hi,
The following code is to select or filter data by clicking a button based on some criteria:
*****************************************
Private Sub CommandButton_Click1()
Application.EnableEvents = True
Dim wsD As Worksheet
Dim wsC As Worksheet
Set wsD = Worksheets("Target Sheet")
Set wsC = Worksheets("Source Data Sheet")

wsC.Range("rangename") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wsD.Range("A1:F2"), _
CopyToRange:=wsD.Range("A10:AD10")

Application.Calculate
Application.EnableEvents = True
End Sub
***************************************
When I run the Sub in VBE, it works; but when I press the button on the target sheet, there is no reponse at all.

Another question. When I say 'it works', it doesn't mean it works well because the criteria range "A1:F2" has 6 criteria and 5 of them working. Only the first one, which is State, not working. I checked the Data Validation, the State list OK; I checked the range names and they look OK too. There is no single error message and I couldn't find a clue.

It's too bad I cannot attach the workbook but I am not sure if I made myself clear.

Thanks in advance.
jqzhang

 

[SkipVought]

Hi,

Are you sure that you have the correct click event?

VB will number the object.

Private Sub CommandButton[red]1[/red]_Click()


Skip,
[sub]
[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it![/sub]

 

[jqzhang]

Skip,
Yes, I am sure because there is only one button on the sheet.
Thanks.
jqzhang

 

[GlennUK]

Hi jqzhang,

you have CommandButton_Click1 instead of CommandButton1_Click surely? I'll bet that's what Skip was trying to point out to you.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[jqzhang]

Skip and Glenn,
Thanks a lot. It works now since I fixed the name of the button. You were right and I was wrong.
By the way, what about the second question. The first field, State, won't show but the rest of fields in the criteria are OK.
I know it's difficult for you to understand what I am saying because I cannot attach the workbook. So what do you think it might be by your experience?
Thanks again.
John Zhang

 

[xlbo]

probably whitespace at the end of the text in either the criteria or the data

Select a cell and click into the formula bar. Press "end" - observe where the cursor ends - if there are spaces at the end of your text, the criteria won't work

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question