?
Solved

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

Posted on 2010-11-08
6
Medium Priority
?
314 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 19

Accepted Solution

by:
Ken Butters earned 2000 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

752 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