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

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)
  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)
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.

Who is Participating?
Ken ButtersConnect With a Mentor Commented:
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.

Ken ButtersCommented:
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.
tindavidAuthor Commented:
Thank Buttersk for your prompt response.

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

Sub openUserForm1()
    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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

tindavidAuthor Commented:
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.

tindavidAuthor Commented:
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.
tindavidAuthor Commented:
Thank you Buttersk. Greate helper!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.