Autofilter.zip && MSflexgrid1
I download the example of the Autofilter.
It is technically possible to do these functions?
1. Load a file into MSflexgrid1 (column 0 only & autofiler) instead on random colors shown in the example file? Once it is load I then update the other columns with a timer.
2. Once the MSflexgrid1 has data in the cells I am able to change the data in the cell but it does not show in the Autofilter. What do I need so both can be updated?
---------------- from autofilter.zip
Private Sub Form_Load()
Dim aColors(4) As String
Dim lCol As Long, lRow As Long
Dim lRnd As Long
' just to fill the grid with some random data
aColors(0) = "Red"
aColors(1) = "Blue"
aColors(2) = "Green"
aColors(3) = "Black"
aColors(4) = "White"
m_lScrollWidth = GetSystemMetrics(SM_CXVSCROLL) * Screen.TwipsPerPixelY
.Cols = 8
.Rows = 40
.AllowUserResizing = flexResizeColumns
.FixedRows = 1
.FixedCols = 0
.SelectionMode = flexSelectionFree
.ScrollTrack = True
.FocusRect = flexFocusNone
.HighLight = flexHighlightNever
.Row = 0
.Col = 0
m_lComboHeight = (.CellHeight / Screen.TwipsPerPixelY) - 3
.ColSel = .Cols - 1
.FillStyle = flexFillRepeat
.Text = "Color"
.FillStyle = flexFillSingle
For lRow = 1 To .Rows - 1
For lCol = 0 To .Cols - 1
lRnd = Int(Rnd * 5)
.TextMatrix(lRow, lCol) = aColors(lRnd)
.RowHeight(0) = cmbFilter(0).Height
' store the default rowHeight
' so we can reset the hidden rows
m_lRowHeight = .RowHeight(1)
<-** Would like to load this instead of the colors -Start
Open "C:InetHistory.txt" For Input As #1
Do While Not EOF(1)
ReDim Preserve txt$(UBound(txt$) + 1)
Input #1, txt$(UBound(txt$))
For Rep = 1 To UBound(txt$)
<-** Would like to load this instead of the colors --End
The above only to fills the first column.
m_bFilterEnabled = True
cmdFilter.Caption = "Hide filters"
Thanks for any help.
Edit by italkid: Please use the [vb]][ and [/vb] tags to display your code, thanks.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
from another post i made i was told about the flexgrid alternative to a list box
currently you can hold down the mouse at the top of the list and highlight all the contents by dragging it to the bottom.
is there a way to only allow the user the option to select 1 item at a time ?
(similarly to the multiselect function of a list box set to "none")
In my flex grid the first column contains page numbers of a pdf.
The page numbers appear like this:
1,23,434 -the next row in the grid might look like this
44,56 -and the next
Now when I click on the grid I need to get the first page.
What is the best way to do this?
ok I tried this but, if there are three or more sets of number in the string it doesn't seem to work.
Dim pos%, strText$, strNewText$ strText = MSFlexGrid1.Text pos = InStrRev(strText, ",") strNewText = Left(strText, pos - 1) MsgBox (strNewText)
How I can cotrol of columns' width for MSFlexGrid1 please ?
Any help will be highly apprecaited.
Many thanks in advance..
MSFlexGrid1 HELP ME PLZ
I am using Microsoft DAO 3.6 Library + Access 2000 Datebase with VB6.
I want to show my Record in MSFlexGrid1 & also want to use MSFlexGrid1
edit my record. help me
Project attahed :...
Edited by - Venoms on 7/8/2003 12:43:18 AM
I fail to userstand why "FORMAT function" it is not working in .textmatrix when I am using msflexgrid for add and update database.
MSFlexGrid1.TextMatrix(i, 7) =format(Myrset("Amount"),"######0.00")
but when using the same for dtpicker it is working fine.
txtEdit.Text = Format(DTPicker1p.Value, "dd/mm/yyyy").
whats the use of msflexgrid1.refresh, coz i thought that if i use then it will clear the grid but that is done from Msflexgrid1.clear so what does refresh do?
MSFlexGrid1 && Paste
I have the below text in the Clipboard. I would like to paste it into MSFlexGrid1. MSFlexGrid1 already has the Stock Symbols in the same order in column0. I only want the numbers to be added they are in column1.
I found some code at http://www.officecomputertraining.co...age19.asp#trim that is for getting text left of the ",".
'Using the functions
Dim sFileName As String, sFileExt As String
Dim sFileTitle As String
sFileName$ = "index.aspl"
sFileExt$ = RightOfChar(sFileName$, ",")
sFileTitle$ = LeftOfChar(sFileName$, ",")
I have had a go with no luck. Could someone provide some insight thanks.
Row Select (MSFlexGrid1)
I was searching the forum but didn't
find exactly what I was looking for...
I wold like to change the BG Color and text color for
a selected Row on a MSFlexGrid1, but the only solution
that I have come up with until now is this: (poor one)
Dim tmpRow As Integer
tmpRow = flgCMEList.Row
If .Rows > 0 Then
For J = 1 To .Rows - 1
If tmpRow = J Then
For I = 0 To MaxCol - 1
.Col = I
.Row = J
.CellBackColor = &HFF0000
.CellForeColor = &H80FFFF
For I = 0 To MaxCol - 1
.Col = I
.Row = J
.CellBackColor = &HFFFFFF
.CellForeColor = &H0&
is there a better way?
MSFlexGrid1 Remove Last Tow Row's
My Q is how to remove last tow row's
I use this code to remove selected row
But i want to remove the last tow row's without specified the number of the row
Im trying to link an MS flexigrid to data1...
when I run the program it spits out the msg " compile error...Method or data not found... and it highlights the MSFLEXIGRID1.FormatString = "......"
heres my code....
Private Sub Form_Load()
'loads the data1 flexigrid
Data1.RecordSource = "select PretechJobNo,DateIn, cameraNo," & _
"Location,FaultCode,FaultDescription,Service, " & _
"Comments from Cam_sevice_2 "
'Format the column width to desired value using the following code...
MSFlexGrid1.FormatString = "^PretechJobNo|^DateIn |^CameraNo|< Location |^FaultCode |<Fault Desciption |<Service |< Comments | "
MSFlexGrid1 And Combo Box
I have combo box. How I can show the number of row in the data grid base on the number of record selected on combo box?
Msflexgrid1 Row Manipulation
Hi vb experts, How do i make my data on a row scroll up or down by pressing a command button or drag and drop the contents of a row to another row? thanks on advance..
MSHFlexGrid1 Vs. MSFlexGrid1
What is the differents between the two?
I need to use a Flexgrid that I can edit each cell, have columheaders and lock the first row from being edited.
which would be best to use?
MSFlexGrid1 And Database
[b] Please help me ....
i am going to submit my project all code almost done.
but i did not sure how 2 link data with grid plz help me.
i am very confused. Not sure what i do plzzzzzzzzzzzzzz
Edited by - saimafarrukh on 7/10/2003 4:01:18 AM
MSFlexGrid1 Stored By FIle I/O
I want to make an MSFlexGrix to be stored into a text file and opened again. The opening part is easy. But how can I save it. I am new to Visual Basic so here is what I have. Any help on this topic is great. Thanks in advance.
CommonDialog1.Filter = "Text (*.txt)|*.txt"
If CommonDialog1.FileName <> "" Then
Form1.MousePointer = 11
Open CommonDialog1.FileName For Output As #1
MSFlexGrid1.Row = 1
Do While MSFlexGrid1.Row <> MSFlexGrid1.Rows - 1
MSFlexGrid1.Col = 0
Print #1, MSFlexGrid1.Text
MSFlexGrid1.Col = 1
Print #1, MSFlexGrid1.Text
MSFlexGrid1.Col = 0
MSFlexGrid1.Row = MSFlexGrid1.Row + 1
CommonDialog1.FileName = ""
Form1.MousePointer = 0
MSFlexGrid1.ocx, Register + Admin
I use the MSFlexGrid1, but at work it required a "admin" to put in c:/windows/system32/MSFlexGrid1.ocx
I then asked for IT people add it in the map. Is is the the right map to plACE IT? But it did'nt work! I had to register the MSFlexGrid1.ocx file, but I can't do it (admin)?
Is there a program that can register the MSFlexGrid1.ocx on a "admin" computer?
Can I use a install program that can add the MSFlexGrid1.ocx file in the right map, and also register it on admin computer? in case which program.
Can VB make the ocx file be included in the exe file?
Moving To MSflexgrid1 Row Using Input Box (text Box).
I am currently working on a program that I populate "MSFlexgrid1" with information. There are 181 rows and three columns. As you can imagine, the rows exceed the visible area and the scroll bars appear for row scrolling.
I would like users to be able to type in a column number and have "MSFlexgrid1" to move that row into view and set focus on it.
I have tried several properties, but cannot find exactly what I need.
I have the following code:
'=========== Start Move Code =============
position1 = val(text1.text) + 1 ' value of text1.text, add one for row position
vcheck = position1 > 180 Or position1 < 0
if vcheck=true then
msgbox "Value must be between 0 and 180"
MSFlexGrid1.Col = 0 ' sets focus on first column
' **** OK, this doesn't work, but it does not give error ****
'============ End Move Code ===============
ANY HELP WOULD BE GREATLY APPRECIATED!!!
DTpicker, Remove Time From MSFlexGrid1
I have a databar form with the DTpicker control. When all data is entered you can view the MSFlexGrid1. When this happens, the date is view with the TIME.
I would like to remove the TIME from the MSFlexGrid1, I have tried dtpCustom with dd/MM/yyyy, but this changes the date.
MSFlexGrid1 Change Column Width
when I display a MSFlexGrid ..... I can set it up so that user can alter the width of the columns ....
however I can find no way to trigger something IF a user does this
I have some labels that display totals .... using colwidth(colNo) I can position the labels under the colums ...
However ... if user changes the width of a column I can only change the label position by continually checking on Colwidth ....
Does any body know what to use to trigger the change of label position
Completely Row Selection With MSFlexGrid1.MouseRow
Maybe someone can help.
code : MSFlexGrid1_MouseUp ... vbRightButton .... MSFlexGrid1.MouseRow.
By click with Left Button ... the completely row = selected
By click with Right Button = just one selection in de first column of this Row.
I searche the code for selection the completely row with Right Button .
Cannot Load Control MSFlexGrid1; License Not Found
This application is supposed to read the database (DB2) and display the records on MSFlexGrid, when I try to debug it, it runs until it reaches the following line then it throws an error (Run-time error 424: object required).
Please help as I have been stuck on this for long time.
Private Sub display_Data()
Dim row As String
Dim index_no As Long
MSFlexGrid1.Rows = 1 //Line where the error thrown
MSFlexGrid1.ColWidth(0) = 1000
MSFlexGrid1.ColWidth(1) = 5700
MSFlexGrid1.ColWidth(2) = 1500
MSFlexGrid1.Width = 8400
MSFlexGrid1.TextMatrix(0, 0) = "Report #"
MSFlexGrid1.TextMatrix(0, 1) = "Report Name"
MSFlexGrid1.TextMatrix(0, 2) = "Report Paper Size"
I was just wondering is it possible to loop through the criteria values in an auto filter in excel without knowing the actual value thats been filtred.
For example can you use an index for the criteria instead of hardcoding in the value like this
Selection.AutoFilter Field:=4, Criteria1:="110071"
So Im just wondering is it possible to do something like
Selection.AutoFilter Field:=4, Criteria1:=1
where 1 is an index for the first criteria value
Set Autofilter To All
I have the following code attatched to a button
Private Sub CommandButton2_Click()
Dim conResponse As String
conResponse = MsgBox("Are You Sure You Want To Save Data To That Month?" _
, vbQuestion + vbYesNo, "")
If conResponse = vbNo Then Exit Sub
Dim x As Long
x = WorksheetFunction.Match(cbomonth.Value, Sheet2.Range("A:A"), False)
Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.ScreenUpdating = True
The data that is being copied has an auto filter attached to allow the user to refine the list to enter information against and then store it... So I need to set the autofilter to "all" before the data is stored.
I have tried recording a macro for this but it only contains..
I cant find any similar posts???
Selection.AutoFilter Field:=12, Criteria1:=PostcodeSector
Selection.AutoFilter Field:=13, Criteria1:=NeighbourCluster
JobNumber = Range("A3").Formula
Im new to the forum and have a problem i hope you can help with.
here is the snippet of my code that does not work.
I understand why it does not work (A3 is filtered and so is hidden)
what im actually trying to acheive is select the first line of the first column in my autofilter...and i have no clue on how to do it?
any help is greatly appreciated.
Next Row When AutoFilter Is On
Move the ActiveCell to the next row is simple:
Ok. But if AutoFilter is on, that command can lead me to a non shown cell.
How can I do if I only want to iterate across the shown cell?
I would appreciate very much a help. Thanks.
I keep getting "Application-defined or object-defined error" on the second line when running this code. I took it from a macro.
.Columns("I:I").AutoFilter Field:=9, Criteria1:=">=31/03/2004", XlAutoFilterOperator:=xlAnd
how should I be implementing the AutoFilter method? Thanks
I am need to autofilter data in column I3 downwards to search for fields with information in them and then delete those rows and then leaving all of the others visible.
AutoFilter For VBA
Thanks for the advise on editing a MSFlexGrid in VBA FlyGuy.
I downloaded your code on AutoFiltering using Comboboxes and was wondering if anyone knows how to make it work in VBA because it would be great to incorporate it into the FlexGrid program that I am building for work in the Autocad environment to filter Parts List?
I know that the Userform needs to be resizeable and the Comboboxes placed in the correct columns of the FlexGrid as well. One problem that I see is that Control Arrays were used in FlyGuys' code, but will not work in VBA.
Look forward to hearing from someone soon.
i have a database in excel in which i use autofilter ie.
selection.autofilter field:=12, criteria1:="11" (11 could be john!!)
i am looking for a way in which i can change the criteria and still send the results to various sheets. the database is 3000>10000 lines long and variable at present i have to repeat the filter up to 12 x with a slowing down of the resuls
any ideas on how i could achieve this
Turning Off Autofilter In Vba
I have a worksheet that has filters I wish to remove the filter to ensure that when i copy all the data to another sheet using code there are no errors
thanks for your help
Excel AutoFilter VBA
I have data that must be separated by Job number to separate worksheets. I have used the Advanced Filter to identify the unique records and copied this data to a location at the bottom of my spread sheet. I then want to use the AutoFilter to loop through the unique records and copy the results to other worksheets. I am, however, unable to come up with the correct code. This is what I have so far:
Dim intJobNumber As Integer
' Identifies Unique Data
Columns("C:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
' Loops Through Unique Data and Copies to Own Worksheet
For Each intJobNumber In Worksheets("Call_Report_Info").Range("A500001", Selection.End(xlDown))
If intJobNumber.Value > 0 Then
Selection.AutoFilter Field:=3, Criteria1:=ActiveCell
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
ActiveWindow.Zoom = 65
The probelm, I believe, has to do with the variable. Can someone please point this novice in the right direction?
I am having problems with autofilters applied by code, in that the condition I apply to the autofilter is not actually being applied:
.AutoFilterMode = False
.Range("E6:AC6").AutoFilter field:=1, Criteria1:=shtEmail.Cells(31 + i, 6).Value
Always Return 0 In Autofilter?
My Autofilter returns correct row number after autofilter: the first row number after filtered previously. But now, it returns always zero for timeFromRow. I have not change anything. The same code. Why now it just returns zero? Is anything wrong with my Excel itself?
Would someone be kind to help me out?
Thank you very much!
Set rng = .UsedRange
.AutoFilter field:=18, Criteria1:="<" & getTimeFrom
On Error Resume Next
timeFromRow = .Offset(1, 0).SpecialCells(xlCellTypeVisible).Row
AutoFilter && Sort
I have following Data in a Worksheet:
Price :: Status :: Item
When i filter the data via 'Item' i want that the data of 'Status' and 'Price' are sorted automatically.
Is there a way to do that automatic, or do i need an external event? (like the pushing of button?)
Thx in advance!
Problem With Autofilter
I have the below code which filters a column in another workbook for the number 17, then copies the selection to a sheet in my workbook.
Workbooks("Month End Data 2004 posest.xls").Activate
UserInput1 = InputBox("Please enter the sheet name")
Dim myRange As Range
z = Range("a65536").End(xlUp).Row
Set myRange = Worksheets(UserInput1).Range(Cells(2, 1), Cells(z, 24))
My problem is that if the number 17 doesn't appear, I need a msgbox to appear and say something like "The number 17 not found". I have tried using IF statements with the autofilter criteria, but it doesn't seem to recognize.
Is there anyone that can help me with this?
Hi, im tryin to display the Autofilter criteria IF the autofilter is being used on a worksheet but im having some trouble to make this possible, i think this is not a BIG problem but i really some help here.....tks.
Autofilter And Listbox...
Hi there. Can anyone please help me to sort out the problem?
Range("n1").Select 'containing values of dates
X = 1
Do While ActiveCell <> ""
X = X + 1
'Right here, I want to display only those dates in column(n) > Date in the listbox3
'but I don't know how to. I tried something like this but to no avail:
Worksheets("Main").AutoFilter field:=14, Criteria1:=">"&Date
For i = 1 To x
.List(i - 1, 0) = Format(Cells(i, 13).Value, "0.0")
.List(i - 1, 1) = " " & Format(Cells(i, 14).Value, "dd/mm/yyyy")
Get The Row Number Using Autofilter?
I tried to use autofilter to do some kind of search work. If the data sheet has the number, I would like to know the row number of the row. If there are batch of rows satisfied the criteria, I would like to know the row number of first row using VBA. Would someone be kind to let me know how I can do that?
Thank you very much!
Using An Autofilter In A VB Macro
sorry if this appears to be a dumb question, but i am pretty new to this game.
i am trying to write a macro to look down a list in a autofilter (xl spreadsheet) and then seperate the data on to new work sheets. The this is, the data may be called different things each time. also i cant work out how to loop so that it moves down the list until it is complete.
any body help on this one!?
Please Help About Autofilter With MSFlexGrid
I asked this question before but i could not get any answer.
I would like to autofilter the my records(with texbox change event) and show the records on MSFlexGrid immediately.(I am using Access database and ADO control)
I found a few sample codes with DataGrid,but I would like to do this with MSFlexGrid.
I'm having a lot of trouble implementing the autofilter method. And the VBA help isn't helping much.
Check the attached pic for my spreadsheet
I want to filter the rows with ID > 3
Then I want to delete these rows and get the other rows back (without the ones that were filtered and deleted).
This is what I tried:
Selection.AutoFilter Field:=1, Criteria1:>3
Can I use the name "ID" instead of "Field:=1"?
Other filters I want to do:
-Delete all rows except the ones with date=10/12/2003
-Delete all rows with code greater or equal B (not by using C).
Specifying Column With Autofilter
Any help would be appreciated. Thanks in advance.
I am trying to reference a specific column while autofilter is on. My current code (created with the macro recorder) targets the column by field number.
Selection.AutoFilter Field:=19 , Criteria1:="<>"
The problem is that when I add or delete a column, the targeted column will become 18 or 20, but the macro still points to column 19. How can I reference a column in permanent way?
Counting With An Autofilter On
How would i do a count of 2 vaules when i have an autofilter on?
I want to do a count of the values "RFA" in the column, and then do a count of "MCA" in the column, but this is after i have used an autofilter and is still on. I then want to place the count of these 2 values in another workbook. I want to do the count on about 50 worksheets in the same workbook and have the values returned to the 2nd work book. Any suggestions? Thanks
So what's the deal with this Autofilter problem:
I have my workbook set up with the top 1051 rows done up for new entries (via UserForms). The current row count for new entries is only about 65...
Now, a few rows below the bottom of the "New" entries section is a another 300 rows of data linked through one of our Access databases.
So one of my UserForm features allows the user to do quick & easy Custom AutoFilters (they'll soon get the whole nine yards), but the problem is, Excel doesn't include any of the Access linked Data in the filter.
Am I missing something? Do all of those 980 something blank lines confuse the AutoFilter? Is there any code that I can use to have Excel look past those blank lines to the next set of data?
I'm gonna keep tinkering with this but if anyone has ideas that would be great...