473,238 Members | 1,898 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,238 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 1782
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

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

Similar topics

6
by: Patrick | last post by:
Following earlier discussions about invoking a .NET class library via ..NET-COM Interop (using regasm /tlb) at...
3
by: Alex Stevens | last post by:
I'd already posted this in microsoft.public.dotnet.framework.windowsforms and microsoft.public.dotnet.framework.windowsforms.controls to no avail so apologies for the cross-posting. Hi, I'm...
13
by: Dixie | last post by:
How do I, in VBA from an access form module, add 5 rows to the top of a spreadsheet called MySpreadsheet.xls. The worksheet is called MyWorksheet and there is already data in the first 5 rows. I...
3
by: George | last post by:
Sub ExcelToListBox() Dim xRange As Object Dim ary Dim xValue As String xRange = oXL.Range("A1:A9") 'has letters A-H ary = xRange.value xValue = ary(3, 1) 'xValue = C...
1
by: gssstuff | last post by:
Hi I have a need to send data from Access to an existing Excel template that I am using for reporting purposes. The nature of the data in the Access data table does not lend itself to a simple...
3
by: Al_C | last post by:
Hi, We have a few apps that we would like to invoke from withing our program. Suggestions? Should I create a seperate thrread to run the app in. The idea is to call excel for example from within...
4
by: vancehome | last post by:
I have a series of macros running everynight creating tons of excel files (using transfer spreadsheet). I then have another program that zips them up and emails them all over the place. It then...
18
by: Zytan | last post by:
I want the same function to be run whether you press Enter or double click the listbox. It seems really verbose to write both handlers to both events everytime, even if they both call the same...
5
by: hurricane_number_one | last post by:
I'm trying to have a class, which uses threads be able to raise events to the form that created it. I've seen solutions around the net for this, but I didn't really like their implementation. ...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.