PDA

View Full Version : Solved: dual purpose TextBox string



Aussiebear
04-16-2010, 11:48 AM
Earlier, I was having an issue getting an advanced Filter to work on sorting data that fell within the Australian financial year, however after a couple of beers, Excel & I have reached agreement with the following workaround.

Cell B11 to LastRow contains a series of dates
In a helper column (Cell N11)

=If(Month(B11)>=7,Year(B11)+1,Year(B11))

But now I was wondering if in the advanced Filter Criteria (with is filled from a form with a series of Textboxes) if txtTransDate.text equals dd/mm/yyyy format then it fills cell B3, or if txtTransDate.text equals yyyy format it fills cell N3. Is this possible?


Sub cmdFilter_Click()
'Set Values to Filter Data on
With Worksheets("Dividends")
.Range("A3").Value = txtASXCode.Text
'Check string format to determine location of textbox input
If txtTransDate.Text = "dd/mm/yyyy" format then
.Range("B3").Value = txtTransDate.Text
else
If txtTransDate.Text = "yyyy"formatthen
.Range("N3").Value= txtTransDate.Text
End If
.Range("C3").Value = txtTransType.Text
.Range("D3").Value = txtUnitPrice.Text
.Range("E3").Value = txtUnitsGained.Text
.Range("H3").Value = txtDivRate.Text
.Range("J3").Value = txtDivEarned.Text
.Range("K3").Value = txtFranking.Text
End With
Call Filter_Dividends
End Sub

The reason being that it could give two choices to advance filter the data on, a particular date or a financial year.

Bob Phillips
04-16-2010, 11:59 AM
If you know the values in the textbox will be valid, test it for a date



If IsDate(txtTransDate.Text) Then
.Range("B3").Value = txtTransDate.Text
Else
.Range("N3").Value= txtTransDate.Text
End If

Aussiebear
04-16-2010, 12:45 PM
Thanks Bob.

Aussiebear
04-16-2010, 12:52 PM
Whilst this works, Excel remains defiantly obstinant on the advanced filter function

:banghead:

mdmackillop
04-17-2010, 11:51 AM
Hi Ted,
Can you post a small sample?

Aussiebear
04-17-2010, 12:34 PM
Hi Malcolm, its back here in this thread.

http://www.vbaexpress.com/forum/showthread.php?t=31135&page=2

mdmackillop
04-17-2010, 02:04 PM
I replied in that thread