Posts by mobygrid

mobygrid · Jan 7th 2011

Re: Listbox column heads dont display

thanks rory, I dont think I will ever forget this one..

mobygrid · Jan 7th 2011

Hi everyone,
I am trying to diplay the first row as the column heads but instead listbox displays Column A, Column B etc. Probably it has an easy trick to do but I have been struggling for long.
Would you help me to verify what is wrong with the below code?
Thanks
OGZ

Code

Dim rng
Set rng = Sheets("Task_List").Range("a1:f5")
    With ListBox1
        .RowSource = rng.Address
        .ColumnHeads = True
        .ColumnWidths = ";0 pt;"
    End With

mobygrid · Jan 7th 2011

Re: Filter Named Range To Userform ListBox

Thanks Krishnakumar, very good piece of code for me..
Oguz

mobygrid · Oct 24th 2010

Re: rowsource definition with worksheet range

Thanks Krishnakumar, that helps

mobygrid · Oct 22nd 2010

Hi,
I have tried a hundred of notation but none worked, would you help what is wrong with below piece?
thanks

Code

LB_Module.RowSource = "SourceWB.Sheets(1).range(""C1:C" & WorksheetFunction.CountA(SourceWB.Worksheets(1).Range("C:C")) & """)"

mobygrid · Oct 21st 2010

Re: double quote usage with Evaluate function

You are opening my vision vision.. thanks a lot..

mobygrid · Oct 21st 2010

Re: double quote usage with Evaluate function

Thanks Rob, great help! Especially the first one is very interesting. appreciate that.

mobygrid · Oct 21st 2010

Hi Everyone,
I had lots of help over years, so thanks to all contributers, I am trying to feed the forum back in these days;
Below small piece of code does not work, any idea?
thanks

Code

MsgBox Evaluate("=CountA(SourceWB.Worksheets(1).range(""B:B""))")

neither this;

Code

MsgBox Evaluate("=CountA(SourceWB.Worksheets(1).range("B:B"))")

mobygrid · Oct 20th 2010

Re: How to run SQL SELECT from VBA and display the results in the simplest way

This code gets data from another excel file and put it into a userform which has a listbox "lisxbox1"; treat this as a starter not an expert code piece ..
[INDENT]

Code

[INDENT]'define DataBase_FileLoc and DataBase_Filename, where you get the data (I use an excel file)
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & ThisWorkbook.DataBase_FileLoc & ThisWorkbook.DataBase_Filename & ";Extended Properties=Excel 8.0;Persist Security Info=False"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open
If SQLfrom <> "" Then SQLfrom = " WHERE " + SQLfrom
'SQLfrom has arguments like "WHERE XXX = 'YYY'"
'Data is the name of the excel sheet!!!
SQL = "Select no1, engine, module1, data, part_no, part_name, sender from [Data$]" & SQLfrom
Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open
No_Records = False
If oRS.EOF Then
MsgBox "DEBUG: No records matching the filter!" & Chr(13) & "Either change or clear the filter!", vbOKOnly, "No Match Found"
No_Records = True
Else
With oRS
'Set .ActiveConnection = Nothing 'Disconnect the recordset.
k = .Fields.Count
'Populate the array with the whole recordset.
vaData = .GetRows
End With
End If
'array transpose
Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
Dim tempArray As Variant
Xupper = UBound(vaData, 2)
Yupper = UBound(vaData, 1)
ReDim tempArray(Xupper, Yupper)
For X = 0 To Xupper
For Y = 0 To Yupper
tempArray(X, Y) = vaData(Y, X)
Next Y
Next X
TransposeDim = tempArray
 
With Data_Table.ListBox1
.Clear
.ColumnCount = k
.List = TransposeDim
.ListIndex = -1
End With
If No_Records Then Data_Table.ListBox1.Clear
[/INDENT]

Display More

[/INDENT]

mobygrid · Oct 19th 2010

Re: Progress meters on steriods !

Andy I love you too

mobygrid · Oct 18th 2010

Re: Non-activex Datepicker Calendar Control

Thanks.. appreciate it.