Posts by monarchmk

Runtime 91 in search pattern in Excel VBA (and in your code) is error if nothing is found. Use

On error resume next 'Enable error handler
Selection.Find ... 'Your find command with corrected parenthesis
On Error goto 0 'Disable Error handler

You have no problem. Except this errors down. Parenthesis closes on wrong location. (I assume that this is only error when you put code here as Excel will make error pop up for this also )

mailmwo.Selection.Find(What:=mwonum[B])[/B], After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ ' <- line generated by Excel macro record

It should close before .Activate on find like this

mailmwo.Selection.Find(What:=mwonum, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
 False, SearchFormat:=False[B])[/B].Activate

red lines are lines that I had to add to get to .Activate

Dear Sir,

Thanks for replying sir, "LstName" is the name of pop up menu. In my previous thread also some respected member suggested to use label. But my requirement is not that actually. I think i will not get the appropriate answer here. Any way, thank you once again sir.

giving up so easy?...

What about TextBox? If you do not like Label, use TextBox. If you make it property Flat(appearance=0) and BorderStyle=0 (none) it will look same as PopUp. Even with standar settings looks almost same as PopUp. Down you can find same code for textbox control with your measurements for position same as PopUp. Even you can select text or parts of text inside.

And if must be PopUp, there must be some specific reason. Let us hear it, maybe if this could not be solved with popup, We can try find similar control for you!

Private Sub List1_KeyUp(KeyCode As Integer, Shift As Integer)
    If Len(List1.Text) > 1 Then
        Text1.Text = List1.Text
        Text1.Width = Len(List1.Text) * 200
        Text1.Height = 400
        Text1.Left = List1.Left + List1.Width / 2
        Text1.Top = List1.Top + List1.Height / 2
        Text1.Visible = True
    Else
        Text1.Visible = False
    End If
End Sub

easy :)

replace line 18 (Line Input #1, LineA)
with this code

While Not EOF(1)
              Line Input #1, LineA 'Since all dates are same i will read only 1st row
        Wend

it will loop until it is end of File (EOF) 1 in parenthesis is index number of file that you opened in line 17.

And please mark thread SOLVED if everything is OK.

Let me try from some different angle...

Employee names are in cells B14 to B23
In sheet Holidays, Employees names are in column L (12 column) mentioned in Autofilter in row 18th.
So you need to count how many times Employee name is listed in columns 12 of sheet Holidays?
If this is correct you do not need arrays..
In every cell from C14 to C23 put this formula:

=COUNTIF(Holidays!L2:L523;B14)

This is sample for C14, in C15 change B14 to B15 etc...
range in holidays sheet is where i think that names are, so you can change this rang.
P.S. After some readin of your code i noticed that you try to make subtotal from column 13 (M column). If you need SUM instead of COUNT of cells then use this formula

=SUMIF(Holidays!L2:L523;B14")

Remember that this is formula for C14, you must change B14 to B15 for next row...
Everything is same as with countIF except that this formula makes SUM
and if you like to use different range like sum of M but criteria from L cells than use this formula

=SUMIFS(Holidays!M2:M523;Holidays!L2:L523;B14)

This is a bit more complicated but below you can find explanation. This sums all numbers in M columns, only from rows that have value equal to cell B14 in column L (from 2 to 523 row)
CountIF
COUNIF(range, criteria) - counts occurrences in cell range based on criteria
SUMIF
SUMIF(range,criteria) - makes …

Ok..

You need,
One Listbox named List1.In property of ListBox please change MultiSelect to 1 (as it is readonly in runtime so i could not change it in code)
One CommandButton named Command1
and this code... (put it in form code, Code replaces FORM_LOAD and COMMAND1_CLICK procedures) If you have procedure for filling list1, do not use mine, use only parts that you need.

Private Sub Form_Load() ' On form loading do...
folder = Dir("e:\vbtest\*.txt") 'variable folder conatins names of files in folder e:\vbtest\ with extension *.txt
    While Len(folder) <> 0 'loop while length of folder is 0 which means no file found...
        List1.AddItem Left(folder, InStr(folder, ".txt") - 1) 'add file to list, but trim .txt extension..
        folder = Dir() 'read again
    Wend

End Sub 'End of form load


Private Sub Command1_Click() 'After selecting id's in listbox, you will click on command button 1, this is procedure
Dim IDs As String

For i = 0 To List1.ListCount - 1 'do loop within all od list1 indexes
    If List1.Selected(i) Then 'If checked index is selected then ...
        FileName = "e:\vbtest\" + List1.List(i) + ".txt" 'Construct filename, add folder and extension
        Open FileName For Input As #1 'Open file for reading
             Line Input #1, LineA 'Since all dates are same i will read only 1st row
        Close #1 'Close file
            If DateDiff("d", CDate(Left(LineA, 10)), Now()) > 2 Then 'If difference between now and readed date is more than 2 days ("d")
                IDs = IDs & IIf(Len(IDs) > …

Ok, here is some sample code...

You need
Listbox (in sample ) List1
Command button Command1 in sample just for activating
For Path I used C:\###.txt - Please Change in Line 11
Date parser is in row 15 but it is just sample, i do not know date format that you are using
And i still does not have a clue what to display in msgbox :?:

Private Sub Command1_Click()
Dim DateA()
Dim DateRow As Integer
ReDim DateA(List1.ListCount)

DateRow=1

For I = 0 To List1.ListCount - 1

    If List1.Selected(I) Then
        FileName = "c:\" + List1.List(List1.ListIndex) + ".txt"
        Open FileName For Input As #1
        Line Input #1, LineA
        Close #1
        DateA(DateRow) = Left$(LineA, 10) ' This is sample for date parser ...
        DateRow = DateRow + 1
    End If

Next

'Now here you have
'DateRow - how much dates you have
'DateA() - dates retreived from files...
'You can use 
'For i = 1 to DateRow
'     msgbox DateA(i)
'next
'or something like this :)
End Sub

What you need to be displayed in MessageBox?
Is date same for every row in file?
Is the location of all files in same folder?
Format of the date? dd/mm/yyyy or dd-mm-yyyy or dd,MMMM YYYY or similar? example maybe?

When you check ID's, you will get dates of every ID and then what would you lake to be done with those dates?

You have existing list1 and lstName. by properties that you use, I think lstName is label. If you can use that or any other label, please check following code
On every list1 item that is longer than 18 characters it will show label above list1. And hide it if length is < 18 characters.
Some of the code inside this procedure (like width. height of label) is not intend to be here but for sake of the sample i must use it this way.

Check is this works for you...

Private Sub List1_KeyUp(KeyCode As Integer, Shift As Integer)

            'lstName.Caption = List1.Text
            If Len(List1.Text) > 18 Then
                lstName.Caption = List1.Text
                lstName.Width = Len(List1.Text) * 100
                lstName.Height = 300
                lstName.BorderStyle = 1
                lstName.Left = List1.Left
                lstName.Top = List1.Top - lstName.Height
                lstName.Visible = True
                lstName.ZOrder (0)
            Else
                lstName.Visible = False
            End If
                
End Sub

If it is a length problem, why not you use a Frame (empty captioned) with label inside (only label can not be put in front of listbox)? There is much less complications.

If my list box item is too long, and that is not visible in my list box because of list box width.

Then, you want to put all invisible (items out of bounds of list box) items in popup menu?

If this is point, why not put columns in list box?

Also how big will be popup window?
popup and List-box sizes are almost same size, so i do not thing that you will gain space using popup and not enlargeing listbox. Except on small forms.

now in select add SrNo like this

Adodc4.RecordSource = "select  SrNo,RollNo,Name, " + cmbMonth.Text + " from LecturesAttended where SubjectName='" + cmbSubject.Text + "'"

and rest of code should work...
But this Syntax will update only current record

If you have , for example , record like this where SubjectName is equal

SrNo, StudentID, RollNo, Name, SubjectName, June,..., March
1 XXX YYY ZZZ AAA BBB
2 XXX YYY ZZZ AAA BBB
3 XXX YYY ZZZ AAA BBB
4 XXX YYY ZZZ AAA BBB
5 XXX YYY ZZZ AAA BBB

After first update you will face this situation
SrNo, StudentID, RollNo, Name, SubjectName, June,..., March
1 XXX YYY ZZZ AAA NewValue
2 XXX YYY ZZZ AAA BBB
3 XXX YYY ZZZ AAA BBB
4 XXX YYY ZZZ AAA BBB
5 XXX YYY ZZZ AAA BBB

Only first record after select is updated... so you should go thru all record with Adodc4.Recordset.MoveNext and applying same changes or use
UPDATE SQL syntax for updating all records that match criteria.

Also one suggestion, is there any possibility to avoid using column for months? In 2 years you will end with 24 columns for months and SQL query's will be hell hard to write, while filter for rows is far more easy to write

glad to help :)

I need some clarification...
Pop up menu show when you push up/down arrow in listbox?
What is the point of using Popup when you already see item in list?
Also Popup menu is kind-of modal form, out of focus turns visible to off

Primary keys does not solve problem, you need Unique Column in database. Add column with autonumber feature and your problem is solved.

And remember that in almost every database you need Unique column. That a way that SQL distinct rows with same values

I do not understand, you need to search while hdd for file and than open it or just single folder? Solution up is for single (known) folder and for partial filename. You can use any name with wildchars like DIR("e:\MyFile2???.txt") or DIR(e:\MyFile2*.txt")

If i understand correctly... This is loop with DIR function thru all files in folder names MyFile2*.txt (eg20001, 20002, 202 etc.)

MyFile = Dir("e:\MyFile2*.txt")
While MyFile <> ""
    Debug.Print MyFile 'Or do whatever you like with file... MyFile variable contain file full name
    MyFile = Dir() 'read new file if it exist... if not loop will end
Wend

Sorry to reopen but solution is wrong... it will again point to out of memory issue...

in select at end you must add WHERE clause.. like this

Select * from RT_TIME WHERE RTTI_DAT_TIME < 'date_time' order by RTTI_DAT_TIME DESC

where 'date_time' is litteral string that depends on your settings and you can see this link for firebird date literals...
http://www.firebirdsql.org/doc/contrib/FirebirdDateLiterals.html
here is a short list od date literals for date 25th of Jun 2004 =

'25.6.2004' '06/25/2004' 'June 25, 2004' '25.jun.2004' '6,25,2004' '25,jun,2004' '25jun2004' '6-25-04' 'Jun 25 04' '25 jun 2004' '2004 June 25' '20040625' '25-jun-2004' '2004-jun-25' '20040625' '25 JUN 04' '2004-06-25' '2004,25,06'

Avoid using * and avoid using select without WHERE, because retrieving a whole DB for only one record is time and resource consuming, with where is faster.

Normaly i did not read whole syntax and wrote SQL for MS databases...
Correct syntax for Firebird is
SELECT FIRST 1 from rt_time ORDER BY <orderfield> DESC

this should solve issue (i hope:) )

select top 1 * from rt_time order by ID DESC

You can change ID in whatever column which is criteria for last record (eg. date, ordernumber...), and DESC means order from last to first, and top 1 means return only first record (which is last record in database with desc order).This passes job on server not on you software.
Also try put some indexes on lager tables, it will work faster

maybe in header cell you can put...
=IF(COUNTIF(B2:B8;"<35")>=2;"FAIL";"PASS")

where B2:B8 is range
"<35" is criteria for Fail...
CountIF(range,crit) gives number of cells in range which meets criteria

When you put Maximum iterations to 1 that means with every cell change there will be only one level of recalculation in worksheets. Your cell B2, with every cell change or manual recalculate with F9, will receive A2 value + B2 value. You will not need to change cell values, simple double click and move focus to another cell or F2 and Enter combination, on any cell, will initiate recalculation which will add another A2 value in B2 cell. That why with any change anywhere in sheet your value in B2 changed. Any change in worksheet, triggers Excel to recalculate whole worksheet and that main reason why value in B2 changes whole time. If you put eq. 100 iterations, B2 cell will get 100times A2 cell + B2 cell. This is NOT correct way of calculating cell. If you made circular reference in any other cell in worksheet you will get same "phenomenon". You will probably need VBA to achieve you original idea.
You can test your problem with this...
If you put cell like this
B2: =A2+B2, C2: =B2+C2, D2: =C2+D2 (all three with circular references) and you put iterations to 1... and put 1 on cell A2 with each recalculation (press F9) will get these values
A B C D
1 1 1 1
1 2 3 4
1 3 6 10
1 4 10 20
1 5 15 35
1 6 21 56

it will be …