PDA

View Full Version : VBA Performance Query



avadhutd2
10-27-2009, 06:23 AM
Hi,

I have a query about performance.

Let me give some background first.
I have a listbox that have 2 columns. First column is having "Name" and another is "Data Type".

I am having another listbox where I can transfer items from LISTBOX 1 to LISTBOX 2.

I am forming a SQL query based on selections available in LISTBOX 2.
That is....if user selects 3 items ...A, B & C then I will be having a query as -
SELECT A, B, C from DBNAME

My query is as follows:

If I have to format the entire column for the selected item in LISTBOX 2 in mm-dd-yyyy format, if that selected item if of data type as "DATE".
How can get it done?

Also, if I get to know that the selected item is a DATE then I may have to do coding somewhat as...

If UserForm2.ListBox2.List(1, 1) = "DATE" Then
Sheet1.Cells(2,2).select
ActiveCell.Columns.EntireColumn.Select
Selection.NumberFormat = "mm-dd-yyyy"
End If

I have seen that doing selection & later on working on selection causes performnce slow down.

Can any one guide me to get this requirement implemented??

Bob Phillips
10-27-2009, 06:45 AM
The select here is almost inconsequentail, but still unnecessary



If UserForm2.ListBox2.List(1, 1) = "DATE" Then
Sheet1.Columns(2).NumberFormat = "mm-dd-yyyy"
End If

avadhutd2
10-28-2009, 02:19 AM
Hi xld ... thanks for the reply.

I tried the way above but not able to access the value in column 2 of the ListBox2.

Hence I had raised another query "Regarding getting values for Multi Column LISTBOX " today with a sample sheet attached.

It would be great if you could get me some guidance.

Bob Phillips
10-28-2009, 06:10 AM
That should work, but maybe it should be



If UserForm2.ListBox2.List(0, 1) = "DATE" Then
Sheet1.Columns(2).NumberFormat = "mm-dd-yyyy"
End If