Solved

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

Posted on 2010-11-08
6
307 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
ID: 34090251
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
ID: 34090572
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
ID: 34090771
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 19

Accepted Solution

by:
Ken Butters earned 500 total points
ID: 34093659
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
ID: 34099506
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
ID: 34099783
Thank you Buttersk. Greate helper!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

856 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