471,593 Members | 1,616 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,593 software developers and data experts.

Invoking an existing Listbox within an Excel VB code execution

2
I'm a struggling new VB Applications user that has what may be a simple question, but I've been struggling with it.

I'm using a response to a message box YesNo question to cause one of two previously created listboxes to populate. These listboxes are each linked to ranges of cells that appear or are blanked out by IF statements in excel formulas =IF(a1=1,"company name","") - the contents of cell A1 becomes 1 based on an affirmative response to the msgbox prompt. A negative response likewise posts a 1 to a different cell that makes a different listbox populate. As part of my VB IF code, I am trying to activate (execute) one listbox or the other so that an end user can then chose a specific company from list of company appropriate to their response to the message box question. One listbox (HMOs) is located on sheet 1, the other (PHSPs) is located on sheet 2- where the button which executes the below code also resides.

My problem is that I have not been able to create code which recognizes and executes the pre-existing listboxes, and dumps the user into the listbox to await their action (choosing a specific company) - at that point I want the VB macro to end. Currently the best I've been able to do is have the cursor end up in a cell next to the appropriate listbox (which I've commented out below)

My current code looks as follows with the XXXXs indicating where I believe the problematic code needs to exist:

Sub Button2_Click()
sheet9.Range("Number").ClearContents
sheet9.Range("Phspnumb").ClearContents

Dim x As String

x = MsgBox("Do you wish to run a HMO program (yes-HMO,no-PHSP)?", vbYesNo, "heading")

If x = vbYes Then

sheet9.Range("Number") = 1

'XXXXXXXXXXXXXXXX this is were I want The HMO listbox to be invoked

' sheet1.Activate
' sheet1.Range("I1").Select

Else
sheet9.Range("Phspnumb") = 1

'XXXXXXXXXXXXXXXX this is were I want The PSHP listbox to be invoked

' sheet2.Activate
' sheet2.Range("I1").Select
End If


Any help would be most appreciated. Thank you.
Jan 18 '07 #1
2 1693
I'm a struggling new VB Applications user that has what may be a simple question, but I've been struggling with it.

I'm using a response to a message box YesNo question to cause one of two previously created listboxes to populate. These listboxes are each linked to ranges of cells that appear or are blanked out by IF statements in excel formulas =IF(a1=1,"company name","") - the contents of cell A1 becomes 1 based on an affirmative response to the msgbox prompt. A negative response likewise posts a 1 to a different cell that makes a different listbox populate. As part of my VB IF code, I am trying to activate (execute) one listbox or the other so that an end user can then chose a specific company from list of company appropriate to their response to the message box question. One listbox (HMOs) is located on sheet 1, the other (PHSPs) is located on sheet 2- where the button which executes the below code also resides.

My problem is that I have not been able to create code which recognizes and executes the pre-existing listboxes, and dumps the user into the listbox to await their action (choosing a specific company) - at that point I want the VB macro to end. Currently the best I've been able to do is have the cursor end up in a cell next to the appropriate listbox (which I've commented out below)

My current code looks as follows with the XXXXs indicating where I believe the problematic code needs to exist:

Sub Button2_Click()
sheet9.Range("Number").ClearContents
sheet9.Range("Phspnumb").ClearContents

Dim x As String

x = MsgBox("Do you wish to run a HMO program (yes-HMO,no-PHSP)?", vbYesNo, "heading")

If x = vbYes Then

sheet9.Range("Number") = 1

'XXXXXXXXXXXXXXXX this is were I want The HMO listbox to be invoked

' sheet1.Activate
' sheet1.Range("I1").Select

Else
sheet9.Range("Phspnumb") = 1

'XXXXXXXXXXXXXXXX this is were I want The PSHP listbox to be invoked

' sheet2.Activate
' sheet2.Range("I1").Select
End If


Any help would be most appreciated. Thank you.
So you want the user to pick from a collection of companies from a listbox. The collection of companies is determined by the MsgBox question activated.

Now, in Excel, there's two types of Listboxes you can use.

1 which can be implemented on the Sheet itself.

1 which can be implemented on a UserForm.

Can you please state which one you're using first?

Thanks,
halfazner
Jan 25 '07 #2
dan NY
2
I'm, looking to have the end user impliment on the user form, with the result to be a yes/no determinant for the macros direction of execution. Thank you for your response.

So you want the user to pick from a collection of companies from a listbox. The collection of companies is determined by the MsgBox question activated.

Now, in Excel, there's two types of Listboxes you can use.

1 which can be implemented on the Sheet itself.

1 which can be implemented on a UserForm.

Can you please state which one you're using first?

Thanks,
halfazner
May 10 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by George | last post: by
3 posts views Thread by Al_C | last post: by
5 posts views Thread by hurricane_number_one | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.