Dynamic List Box using VBA

I wasn't really sure how to phrase this one -- this isn't your Typical
Drop-Down List.

I have a SELECT statement which queries SQL Server 2005 and displays the
data in Excel.

Let's say that a database lists products that have been delivered to 4 
different cities, on 3 seperate days -- without knowing (in advance) the 
delivery dates, how could I create an input/drop-down field to allow the 
user to specify a particular date?  I envision something like this:

VBA code produces a list box of delivery dates based on a particular product 
and city.  The user selects a delivery date from the list-boxe, and this 
selection gets passed to a SQL Select Statement to return the data to an 
EXCEL spreadsheet.  I just don't know of any way to do this ...

Below is the code I'm using -- 
===================
Option Explicit
Sub DataExtractSpecific()
' Create a connection object.
Dim cnExcel As ADODB.Connection
Set cnExcel = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the DWS_Sales database on the Products Server.
strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=Products;" & _
           "User Id=xxxxxxx;" & _
           "Password=xxxxxx"


'Now open the connection.
cnExcel.Open strConn

On Error Resume Next

' Create a recordset object.
Dim OppNumber As String
Dim sqlCommand As String
'Dim CloseDate As Date
Dim rsExcel As ADODB.Recordset
Set rsExcel = New ADODB.Recordset
OppNumber = InputBox("Please Re-Enter Product Number for the 2nd lookup
query.")
'CloseDate = InputBox("Enter the Purchase Date -- like 2/26/2007 11:44")
sqlCommand = "SELECT * FROM Tracking_Specific WHERE [Product Number] = " +
ProdNumber
With rsExcel
    ' Assign the Connection object.
    .ActiveConnection = cnExcel
    ' Extract the required records.
    .Open sqlCommand
    ' Copy the records into cell A1 on Sheet1.
    Sheet1.Range("A3").CopyFromRecordset rsExcel

    ' Tidy up
    .Close
End With

cnExcel.Close
Set rsExcel = Nothing
Set cnExcel = Nothing

End Sub
===============
Many Thanks (in advance) for any assistance on this.

Shane



0
4/16/2007 3:02:37 AM
excel 39879 articles. 2 followers. Follow

3 Replies
533 Views

Similar Articles

[PageSpeed] 40

Do you have to do a subsequent SQL query, or could you just filter the data 
that you already have?

-- 
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Doctorjones_md" <xxxDoctorjones_mdxxx@xxxyahoo.com> wrote in message 
news:e0cRxO9fHHA.4692@TK2MSFTNGP03.phx.gbl...
>I wasn't really sure how to phrase this one -- this isn't your Typical
> Drop-Down List.
>
> I have a SELECT statement which queries SQL Server 2005 and displays the
> data in Excel.
>
> Let's say that a database lists products that have been delivered to 4 
> different cities, on 3 seperate days -- without knowing (in advance) the 
> delivery dates, how could I create an input/drop-down field to allow the 
> user to specify a particular date?  I envision something like this:
>
> VBA code produces a list box of delivery dates based on a particular 
> product and city.  The user selects a delivery date from the list-boxe, 
> and this selection gets passed to a SQL Select Statement to return the 
> data to an EXCEL spreadsheet.  I just don't know of any way to do this ...
>
> Below is the code I'm using -- 
> ===================
> Option Explicit
> Sub DataExtractSpecific()
> ' Create a connection object.
> Dim cnExcel As ADODB.Connection
> Set cnExcel = New ADODB.Connection
>
> ' Provide the connection string.
> Dim strConn As String
>
> 'Use the SQL Server OLE DB Provider.
> strConn = "PROVIDER=SQLOLEDB;"
>
> 'Connect to the DWS_Sales database on the Products Server.
> strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=Products;" & _
>           "User Id=xxxxxxx;" & _
>           "Password=xxxxxx"
>
>
> 'Now open the connection.
> cnExcel.Open strConn
>
> On Error Resume Next
>
> ' Create a recordset object.
> Dim OppNumber As String
> Dim sqlCommand As String
> 'Dim CloseDate As Date
> Dim rsExcel As ADODB.Recordset
> Set rsExcel = New ADODB.Recordset
> OppNumber = InputBox("Please Re-Enter Product Number for the 2nd lookup
> query.")
> 'CloseDate = InputBox("Enter the Purchase Date -- like 2/26/2007 11:44")
> sqlCommand = "SELECT * FROM Tracking_Specific WHERE [Product Number] = " +
> ProdNumber
> With rsExcel
>    ' Assign the Connection object.
>    .ActiveConnection = cnExcel
>    ' Extract the required records.
>    .Open sqlCommand
>    ' Copy the records into cell A1 on Sheet1.
>    Sheet1.Range("A3").CopyFromRecordset rsExcel
>
>    ' Tidy up
>    .Close
> End With
>
> cnExcel.Close
> Set rsExcel = Nothing
> Set cnExcel = Nothing
>
> End Sub
> ===============
> Many Thanks (in advance) for any assistance on this.
>
> Shane
>
>
> 


0
bob.NGs1 (1661)
4/16/2007 8:44:47 AM
Bob,

It appears to me (but I'm open to alternative suggestions) that I'll need to 
perform an initial SQL query to create the list box, then pass the selection 
from this list box to a subsequent SELECT statement for the final query. 
I'm using a Date/Time stamp for the delivery date, so there isn't a way for 
the user to know the exact delivery Date/Time without selecting it from a 
list box.

I'm not certain that what I'm trying to do here is even possible -- any 
thoughts on this?

Thanks for your prompt reply.

Shane
==================================
"Bob Phillips" <bob.ngs@somewhere.com> wrote in message 
news:eSeLcNAgHHA.4188@TK2MSFTNGP02.phx.gbl...
> Do you have to do a subsequent SQL query, or could you just filter the 
> data that you already have?
>
> -- 
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my 
> addy)
>
> "Doctorjones_md" <xxxDoctorjones_mdxxx@xxxyahoo.com> wrote in message 
> news:e0cRxO9fHHA.4692@TK2MSFTNGP03.phx.gbl...
>>I wasn't really sure how to phrase this one -- this isn't your Typical
>> Drop-Down List.
>>
>> I have a SELECT statement which queries SQL Server 2005 and displays the
>> data in Excel.
>>
>> Let's say that a database lists products that have been delivered to 4 
>> different cities, on 3 seperate days -- without knowing (in advance) the 
>> delivery dates, how could I create an input/drop-down field to allow the 
>> user to specify a particular date?  I envision something like this:
>>
>> VBA code produces a list box of delivery dates based on a particular 
>> product and city.  The user selects a delivery date from the list-boxe, 
>> and this selection gets passed to a SQL Select Statement to return the 
>> data to an EXCEL spreadsheet.  I just don't know of any way to do this 
>> ...
>>
>> Below is the code I'm using -- 
>> ===================
>> Option Explicit
>> Sub DataExtractSpecific()
>> ' Create a connection object.
>> Dim cnExcel As ADODB.Connection
>> Set cnExcel = New ADODB.Connection
>>
>> ' Provide the connection string.
>> Dim strConn As String
>>
>> 'Use the SQL Server OLE DB Provider.
>> strConn = "PROVIDER=SQLOLEDB;"
>>
>> 'Connect to the DWS_Sales database on the Products Server.
>> strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=Products;" & 
>> _
>>           "User Id=xxxxxxx;" & _
>>           "Password=xxxxxx"
>>
>>
>> 'Now open the connection.
>> cnExcel.Open strConn
>>
>> On Error Resume Next
>>
>> ' Create a recordset object.
>> Dim OppNumber As String
>> Dim sqlCommand As String
>> 'Dim CloseDate As Date
>> Dim rsExcel As ADODB.Recordset
>> Set rsExcel = New ADODB.Recordset
>> OppNumber = InputBox("Please Re-Enter Product Number for the 2nd lookup
>> query.")
>> 'CloseDate = InputBox("Enter the Purchase Date -- like 2/26/2007 11:44")
>> sqlCommand = "SELECT * FROM Tracking_Specific WHERE [Product Number] = " 
>> +
>> ProdNumber
>> With rsExcel
>>    ' Assign the Connection object.
>>    .ActiveConnection = cnExcel
>>    ' Extract the required records.
>>    .Open sqlCommand
>>    ' Copy the records into cell A1 on Sheet1.
>>    Sheet1.Range("A3").CopyFromRecordset rsExcel
>>
>>    ' Tidy up
>>    .Close
>> End With
>>
>> cnExcel.Close
>> Set rsExcel = Nothing
>> Set cnExcel = Nothing
>>
>> End Sub
>> ===============
>> Many Thanks (in advance) for any assistance on this.
>>
>> Shane
>>
>>
>>
>
> 


0
4/16/2007 9:01:50 AM
If you do issue another query, you could issue it with SQL such as

sqlCommand = "SELECT * FROM Tracking_Specific " & _
                        "WHERE [Product Number] = " & ProdNumber & " AND " & 
_
                        "               [Date] = #" & Range("A1").Text & "#"

where A1 is the cell with the drop-down date selected.


-- 
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"doctorjones_md" <doctorjonesxxxxx_mdxxxxx@yahoo.com> wrote in message 
news:ubfJ0WAgHHA.5044@TK2MSFTNGP05.phx.gbl...
> Bob,
>
> It appears to me (but I'm open to alternative suggestions) that I'll need 
> to perform an initial SQL query to create the list box, then pass the 
> selection from this list box to a subsequent SELECT statement for the 
> final query. I'm using a Date/Time stamp for the delivery date, so there 
> isn't a way for the user to know the exact delivery Date/Time without 
> selecting it from a list box.
>
> I'm not certain that what I'm trying to do here is even possible -- any 
> thoughts on this?
>
> Thanks for your prompt reply.
>
> Shane
> ==================================
> "Bob Phillips" <bob.ngs@somewhere.com> wrote in message 
> news:eSeLcNAgHHA.4188@TK2MSFTNGP02.phx.gbl...
>> Do you have to do a subsequent SQL query, or could you just filter the 
>> data that you already have?
>>
>> -- 
>> HTH
>>
>> Bob
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my 
>> addy)
>>
>> "Doctorjones_md" <xxxDoctorjones_mdxxx@xxxyahoo.com> wrote in message 
>> news:e0cRxO9fHHA.4692@TK2MSFTNGP03.phx.gbl...
>>>I wasn't really sure how to phrase this one -- this isn't your Typical
>>> Drop-Down List.
>>>
>>> I have a SELECT statement which queries SQL Server 2005 and displays the
>>> data in Excel.
>>>
>>> Let's say that a database lists products that have been delivered to 4 
>>> different cities, on 3 seperate days -- without knowing (in advance) the 
>>> delivery dates, how could I create an input/drop-down field to allow the 
>>> user to specify a particular date?  I envision something like this:
>>>
>>> VBA code produces a list box of delivery dates based on a particular 
>>> product and city.  The user selects a delivery date from the list-boxe, 
>>> and this selection gets passed to a SQL Select Statement to return the 
>>> data to an EXCEL spreadsheet.  I just don't know of any way to do this 
>>> ...
>>>
>>> Below is the code I'm using -- 
>>> ===================
>>> Option Explicit
>>> Sub DataExtractSpecific()
>>> ' Create a connection object.
>>> Dim cnExcel As ADODB.Connection
>>> Set cnExcel = New ADODB.Connection
>>>
>>> ' Provide the connection string.
>>> Dim strConn As String
>>>
>>> 'Use the SQL Server OLE DB Provider.
>>> strConn = "PROVIDER=SQLOLEDB;"
>>>
>>> 'Connect to the DWS_Sales database on the Products Server.
>>> strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=Products;" & 
>>> _
>>>           "User Id=xxxxxxx;" & _
>>>           "Password=xxxxxx"
>>>
>>>
>>> 'Now open the connection.
>>> cnExcel.Open strConn
>>>
>>> On Error Resume Next
>>>
>>> ' Create a recordset object.
>>> Dim OppNumber As String
>>> Dim sqlCommand As String
>>> 'Dim CloseDate As Date
>>> Dim rsExcel As ADODB.Recordset
>>> Set rsExcel = New ADODB.Recordset
>>> OppNumber = InputBox("Please Re-Enter Product Number for the 2nd lookup
>>> query.")
>>> 'CloseDate = InputBox("Enter the Purchase Date -- like 2/26/2007 11:44")
>>> sqlCommand = "SELECT * FROM Tracking_Specific WHERE [Product Number] = " 
>>> +
>>> ProdNumber
>>> With rsExcel
>>>    ' Assign the Connection object.
>>>    .ActiveConnection = cnExcel
>>>    ' Extract the required records.
>>>    .Open sqlCommand
>>>    ' Copy the records into cell A1 on Sheet1.
>>>    Sheet1.Range("A3").CopyFromRecordset rsExcel
>>>
>>>    ' Tidy up
>>>    .Close
>>> End With
>>>
>>> cnExcel.Close
>>> Set rsExcel = Nothing
>>> Set cnExcel = Nothing
>>>
>>> End Sub
>>> ===============
>>> Many Thanks (in advance) for any assistance on this.
>>>
>>> Shane
>>>
>>>
>>>
>>
>>
>
> 


0
bob.NGs1 (1661)
4/16/2007 10:01:04 AM
Reply:

Similar Artilces:

Using variable names for cells
I seem to remember a technique where I could assign a variable name to the contents of a cell so that whenever I wanted to use the contents, all I had to do was call up the variable name. Unfortunately I cannot find the way to set up the process. Any suggestsions or ideas would be appreciated. Thanks and a Happy New Year. -- Take out the trash to reply '05 FLHTCUI Hi dim rng as range set rng=activesheet.range("A1") msgbox rng.value -- Regards Frank Kabel Frankfurt, Germany Ultraglide wrote: > I seem to remember a technique where I could assign a variable name to >...

Excel Drop Down Boxes #2
Question; does anybody know how to add additional data to a previous drop down box? I have created a form that has several drop down boxes a while ago, and due to some information that has been change I need to input more info in the drop down boxes but for some odd reason I cant remember how I executed these actions!! If it helps I am using Excel 2000. -- CRS ------------------------------------------------------------------------ CRS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27444 View this thread: http://www.excelforum.com/showthread.php?threadid=46...

Box when opening File
Quick question...When I open up one of my saved files, Visio automatically puts some kind of box (function box possibly) around my heading and some of the text. I'll take it off and save and it isn't there. But when I close the file and then open it up again later it's there again. I try to delete the box but it deletes my text with it. I can't resize or delete the box (actually conjoined boxes) by itself. Any help would be greatly appreciated. Feel free to call...214-981-6324. Thanks! Try changing the line weight of the shape to none. John... Visio MVP Nee...

How do I use excel names with INDIRECT with charts
Hello, I want to create a Chart that does not directly reference cell-ranges (i.e. A1:A6), but excel-names that make the reference sheet-independent. My aim is to be able to copy one chart to other worksheets, which have their dataareas at the same places like the source-sheet. Problem: This works fine in cells but not in charts Excel name definition: =INDIRECT("R3C2";0):INDIRECT("R3C5";0) Any idea about this? Thanks in advance, Holger. You have to include the sheet name in the final formula that you want XL to use. -- Regards, Tushar Mehta www.tushar-mehta.co...

SBS2003 + Internet Explorer
We have a customer who has an SBS 2003 Premium server with ISA 2004 They have just installed a bolt on to Outlook which searches their mailboxes or something. They have discovered that they cannot get this to work unless the untick the 'Use automatic configuration script' in Internet Explorer. Trouble is each time they re-boot a PC the tick comes back, so sounds like it's a policy setting somewhere. What is this ? Where is it configured and what are the implications of turning it off ? Could it be that rather than turn it off whatever it is ought to be con...

disable MDI window list menu
I created a MDI application. When I create a new child window, it automatically adds window name to the 'Window' menu, i.e., it adds 'Doc1', 'Doc2'... How do I make it NOT do that? Thanks. WJ You can change the default name of your documents (in the string table) or you could remove the Window menu from the top menu, or, I think, you can just hook into OnInitMenuPopup() for the mainframe and remove the entries starting with AFX_IDM_FIRST_MDICHILD. This article might help you get started. In this one they remove all those items and replace them with a popup...

Search box within access or third party addon to do such?
I have an access database with 6 columns. I am putting in an inventory of all of my items with their corresponding sizes and weights, etc. I will have over 2000 lines/records when I am done. What I want to do, is either create something withing Access, or find a third party add0on, that will allow me to search within the fields, and return the corresponding results here is what I have for example category style size width weight notes sample 1abc 5 2.2 2.1 sample 1add 3 2.5 2.1 for turning stock 3bbb 3 ...

Adjusting Text box size
In Access 2007, after creating a form using the wizard, all of the text boxes created for entering data are the same size. When I try to adjust the width of one, they all change width. How can I unlock or change this feature? I have deleted a box, and then re-added it. This sometimes works -- however if you get the size close to the size of the other boxes, it “locks in” again. I appreciate all sincere answers. Ron ah yes....a new feature in 07... in Design View; select the 'Arrange' tab then on the left side of my ribbon is: Tabular Stacked Remove You want t...

can cvs files be opened using excel viewer
i have a user who cannot open cvs files with excel viewer but can ope with the full version of excel...anyone experienced this problem and i so, can you pls post the resolution -- darrie ----------------------------------------------------------------------- darriel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2388 View this thread: http://www.excelforum.com/showthread.php?threadid=37522 There is nothing in the description of the Excel Viewer to suggest that it can read anything other than true XLS files. http://support.microsoft.com/default.aspx?scid=kb;...

Exporting distribution lists
Is thee any way to export distribution lists into something like a spreadsheet? Hello: Which version of Exchange are you using? In 5.5 it is easy to do. 200x is not as easy, but doable. Regards, Martin "Elbryyan" <elbryyan@hotmail.com> wrote in message news:483201c4a09c$dd0a5240$a301280a@phx.gbl... > Is thee any way to export distribution lists into > something like a spreadsheet? Do you keep all of your DGs in one OU? If so you can easily use LDIFDE or dsquery group. "Elbryyan" <elbryyan@hotmail.com> wrote in message news:483201c4a09c$dd0a524...

batch importing of mailing lists to Excel
I've got a mailing list of around 1000 names, stored in both Word and Entourage like this: First Name Last Name <e-mail address> Once I’ve eliminated the < >, do I then have to individually copy & paste (or type) all the info in three Excel columns? Or is there a simpler solution for batch importing of mailing lists to the Excel spreadsheet? "bgsignal" wrote: > I've got a mailing list of around 1000 names, stored in both Word and > Entourage like this: First Name Last Name <e-mail address> > Once I’ve eliminated the < >, do I then ...

Manufacturing BOM Where Used query
Winthin manufacturing there is a where-used inquiry window that will show all BOMs that a given item appears in. You can also expand that view to see what BOMs and item's parent is used in. Some of our BOMs are 10 layers deep. In our environment we need to know which category of finished goods each purchased item is used in. Currently we maintain this manually and store the info in an inventory user-defined field. Over time as the number of our finished goods and purchased parts has increased this has become an unmangeable process. I need to find a way to query the BOM tables a...

How to prevent the same program from running twice using vc?
Try this: http://www.codeguru.com/Cpp/misc/misc/article.php/c299 -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "LeeTow" <fbjlt@pub3.fz.fj.cn> wrote in message news:#gTQPBglEHA.2612@TK2MSFTNGP15.phx.gbl... > > Checkout Bob's tip on this subject at http://bobmoore.mvps.org/Win32/w32tip7.htm -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "LeeTow" <fbjlt@pub3.fz.fj.cn> wrote in message news:%23gTQPBglEHA.2612@TK2MSFTNGP15.phx.gbl... > > See also my essay on my MVP Tips site. joe On Wed, 8 Sep 2...

Utilizing the Print Preview without using a printer
How can I use the Print Preview function and make any necessary changes to my worksheet?..I currently do not have a printer installed, but would still like to be able to have access to the Print Preview functions before I actually print my worksheet. Just install *any* print driver that might be resident on your system, and XL will then enable the "PrintPreview" feature, even though no actual printer is present. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ==========================================...

Multiple Exchange boxes in one domain
Hi all, I have the following setup for my domain: HQ: Server 2003 Exchange 2003 We have a branch office located on another industrial estate 1/2 mile away, and we currently connect to them via WiFi (11Mb) and they are in the same domain and subnet as us. I am looking to add a second exchange box at their site so they can get their mail "locally". When I look at setting it up tho ot seems that exchange will still send the messages across the WiFi due to there only being 1 MX record? Is there a way I can get them to recive their mail directly over their own Broadband link? Also ...

Microsoft Office X cannot start because MS Office is already in use
Sometimes Entourage 10.1.4 shows the message: "Microsoft Office X cannot start because MS Office is already in use. A Office program is being used by MYNAME. Your installation exceeds..." It's crazy because I'm the only user with OSX (10.3.2) and Office X is installed only on my Mac!!! It seems that it happens when Entourage run scheduled actions (i.e. Send and Receive). Pls help me. Thanx. -- Buz ...

Why won't contacts list out in address book
When I open my address book or select the To: or CC: options, my contacts are not listed out. I performed the steps at the below link. The address book appears to be linked to the contact folder since the address book shows the correct contact file in the very top and will search from it, but why aren't the contacts listed for easier selection like I've seen before? Thanks.. http://support.microsoft.com/default.aspx?scid=kb;en-us;287563&Product=ol2002 To give further info, I am running Outlook 2007 and the only message in either the global address list or my sp...

Using a value from a cell inside a formula!
Hi! I've got two cells that contain the start and end row of a matrix in sheet. I want to use these row numbers in a =COUNT.IF formula to count th number of specified instances in this matrix. But my problem is this; how can I use the values in the two cell inside the COUNT.IF formula?? I should look something like this; =COUNT.IF(F"cell1":F"cell2";"=argument") As you can see, the column (F) is specified in the formula but the ro number needs to be fetched from cell1 and cell2. Can anyone help me with the correct syntax? Thanks! -M -- marsupilam --...

Filter to specify which account is used according to email address?
I use outlook for work and personal email but i use two different accounts to send/recive email. I have run into the problem of sometimes mistakenly using my personal account to send work emails. So my question is: Is it possible to specify that addresses that meet a certain criteria be send using a certain account. Say an email to Bob@amarc.mil will be sent using my gmail account whereas a email to Jane@moog.gov will be sent using a yahoo account? Any help/suggestions would really be appreciated! Cheers -Gaiko And my question is, what version of Outlook are you using? The answer = is t...

Distribution List Limits
Hi, I could find an answer to this for Outlook 2002 but not for Outlook 2003. Is the answer the same? We are using SBS 2003. The error is: "The distribution list has reached the maximum size for your network e-mail server. The new member could not be added." List was at 132 members. Not an Outlook question. These limits are set by the server, not Outlook. If your sever hasn't changed, then I imagine the limit is still the same. -- Russ Valentine [MVP-Outlook] "Paddy Ryan" <paddy@fpmt.org> wrote in message news:uOKDg0vaGHA.4196@TK2MSFTNGP03.phx.gbl... ...

IE crashing when using CRM 3
some users are experiencing a lot of IE crashes when using CRM3.0.5300.0 This is when clicking around the CRM and I have a screenshot of the CRM as it crashed if anyone needs this... The diagnostic is below: <?xml version="1.0" encoding="UTF-16"?> <DATABASE> <EXE NAME="iexplore.exe" FILTER="GRABMI_FILTER_PRIVACY"> <MATCHING_FILE NAME="HMMAPI.DLL" SIZE="38912" CHECKSUM="0xD85D870C" BIN_FILE_VERSION="6.0.2900.2180" BIN_PRODUCT_VERSION="6.0.2900.2180" PRODUCT_VERSION="6.00....

List Box Change Event
Hi, I have a list box and have the following code in the worksheet module: Private Sub MyListBox_Change() Sheets("Report").Select End Sub My list box is called MyListBox but when I select it nothing happens. All I want is to go to another sheet after the change occurs. The only thing I can think is the name of my list box is incorrect but it does show as MyListBox in the cell name drop down but it doesnt appear in the defined names. Using 2003. Can anyone see what I am doing wrong? Thanks in advance Martin What sort of ListBox is it, from the Forms to...

Make input in one column determine dropdown list in another.
Is there any way I can make the info thats input in one column (selected by dropdown list) determine which (one of several) dropdown list is used in another column? Use a list formula of the type =OFFSET(IF(E1=1,rng1,rng2),0,0,(COUNTA(IF(E1=1,rng1,rng2))),1) where E1 is the first DD. -- HTH RP (remove nothere from the email address if mailing direct) "gettin-older" <gettin-older@discussions.microsoft.com> wrote in message news:8FF90B6B-F07F-402B-A042-E1B103C40EED@microsoft.com... > Is there any way I can make the info thats input in one column (selected by > dr...

Wildcard in VBA?
I apologize if this is here twice--I don't see the post I thought went through earlier today. Anyway .. I have the following code: If .Cells(X, "H") = "RG" Then .Cells(X, "G").Value = "TRC" End If Can I modify this with a "wildcard" character? In other words, if RG is found anywhere (RG123, RG456, 123RG, etc) within cell H, I want TRC to be placed into column G Thanks! maybe something like this, i just hard coded the variable for testing Sub test() Dim x As Long x = 3 If Cells(x, "H") L...

search box
when I type any information in to the search box, a message comes back saying can not find this info?? That's on every thing I type in. "Fred" <Fred@discussions.microsoft.com> wrote in message news:04AB51B0-9AC7-4632-AB73-B9D315284594@microsoft.com... > when I type any information in to the search box, a message comes back > saying > can not find this info?? That's on every thing I type in. Which "search box" are you using, and what type of information are you seeking? -- Regards Steve. MS-MVP. MAIL. [DTS] UK. http://www.ge...