tindavid
asked on
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)
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.
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.
ASKER
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
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
ASKER
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
Thank you Buttersk.
David
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
Thank you Buttersk. Greate helper!
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