Solved

How to build list of value for user to pick (from static value inside a procedure)

Posted on 2010-11-08
6
302 Views
Last Modified: 2012-05-10
I have a function that contains a list of values (mainly hostname, username and password) like below:
Function available_system_info() As Variant
  Dim result(10) As Variant
  Dim SerName(10) As String
  Dim OraUser(10) As String
  Dim OraPass(10) As String
  Dim DB_name(10) As String
  Dim TnsName(10) As String
  Dim indx As Integer
 
  SerName(1) = "ydcrh04"
  OraUser(1) = "system"
  OraPass(1) = "manager"
  DB_name(1) = "demodb"
  TnsName(1) = "demodb_ydcrh04"
  SerName(2) = "ydcrh06"
  OraUser(2) = "system"
  OraPass(2) = "manager"
  DB_name(2) = "testdb"
  TnsName(2) = "demodb_ydcrh06"
  SerName(3) = "ydcrh07"
  OraUser(3) = "system"
  OraPass(3) = "manager"
  DB_name(3) = "proddb"
  TnsName(3) = "demodb_ydcrh07"
  For indx = 1 To 3
      result(indx) = SerName(indx) & " " & OraUser(indx) & " " & OraPass(indx) & " " & DB_name(indx) & " " & TnsName(indx)
  Next
  available_system_info = result
End Function

at the moment, I am able to create a button with a macro to call this function and display all the value from the list.

Sub select_server_info()
  Dim result As Variant
  Dim indx As Integer
  result = available_system_info
  For indx = 1 To 3
   MsgBox result(indx)
  Next
End Sub


Here is what I want:

I wish to dispaly this list of values in a form format with checkbox function. Once a checkbox is checked,  other checkbox should be unchecked, also the password should not be displayed in the form, the values associated with the checked box will be used to connect an Oracle DB in later stage.



0
Comment
Question by:tindavid
  • 4
  • 2
6 Comments
 
LVL 19

Expert Comment

by:Ken Butters
Comment Utility
Attached is a spreadsheet which will use a button to open a form.

Your routine to intiialize the data will be called... the form will be popluated with a list of checkbox items.  After selecting an item ... hit process item button,and msgbox will show which item you selected.
Book1.xls
0
 

Author Comment

by:tindavid
Comment Utility
Thank Buttersk for your prompt response.

Then how can I use the list of value picked from the check box: for example

Sub openUserForm1()
    UserForm1.Show
    picked_hostname = ????
    picked_username = ???
    picked_password = ???   -- even this value does not show in the form but should still be part of list being picked.
    picked_tnsname = ????
    ' subsequently I will use this information to construnct DB connection
End Sub


0
 

Author Comment

by:tindavid
Comment Utility
Oops ,  I don't know to how recreate the button with the form that has 3 list of values. Can you please alight me ? I can make it work by simply copy the "button" from the book1.xls to my worksheet, but it has a hardlined reference to book1.xls. I guess I need to learn how to create such button->form-select ect.

Thank you Buttersk.

David
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 19

Accepted Solution

by:
Ken Butters earned 500 total points
Comment Utility
There are several ways to pull off all the fields individually.  Probably the easiest is to add the index to the choice in the listbox.  I updated the spreadsheet to do this.

As far as adding the button, it depends upon what version of excel you are using.  For 2003, I think there is a forms toolbar that has the controls on it.... you drag and drop form that toolbar.

For 2007... you have to add the developer tab.  on the developer tab, there is an insert icon.  when you click on it you can insert a variety of form controls and activex controls.   when you right click on the button you can assign macro.

Note: to add developer tab click the orb in the upper right hand corner.... then select excel options from the button along the bottom.

Next select "Popular from the right hand column.

one of first few checkboxes on the main page will be Show Developer tab in the Ribbon.



book1.xls
excelOptions.jpg
0
 

Author Comment

by:tindavid
Comment Utility
Hi Buttersk,
This is my first time dealing with a userform creation in Excel, I guess I am lost  as I cannot recreate the same button and assoicated form (like the one you have created). It will be much helpful if you can send  a screenshot or video of how to do it. Oherwise I would need a much longer time to figure how to do it.

Let's see.
Thank you for your help.
0
 

Author Closing Comment

by:tindavid
Comment Utility
Thank you Buttersk. Greate helper!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now