lovemonkey16
11-14-2012, 04:05 PM
Hello
First my apologize if this question was asked in another post, but I have not been able to find an answer in my search so far.
As the title of this thread implies, I am looking for help in how to access entries found in a combobox, which is located in one Userform, from another different Userform. Below is a more detailed explanation:
Program Being Used: Microsoft Excel 2010
Within an Excel Workbook I launch a macro which loads Userform (for ease lets just call it frmUser1) by declaring a variable within a Subroutine (Macro1) within a Module (Module1).
Sub Macro1()
Dim myForm As frmUser1
Set myForm = New frmUser1
myForm.Show
Unload myForm
End Sub
On Userform frmUser1, I have a label (lblPrompt), a ComboBox (cboVersion), and a Command Button (cmdNext)
The ComboBox is populated with a list of column titles for on a Worksheet within the Excel Workbook, and also the column index number (column A = 1, B = 2,...). There is also a "<new>" entry which is placed inside the ComboBox in case the user wants create a new version.
Dim intColumn As Integer
intColumn = 1
cboVersion.AddItem "<new>"
cboVersion.List(cboVersion.ListCount - 1, 1) = -1
While (intColumn < 10) ' I just initially do the first 10 columns for testing, right now.
Dim strColTitle As String
strColTitle = ActiveWorkbook.Worksheets("Versions").Cells(1, intColumn)
If ( 'Version numbers are in the correct format, and not a blank title' ) Then
cboVersion.AddItem
cboVersion.List(cboVersion.ListCount - 1, 0) = strColTitle
cboVersion.List(cboVersion.ListCount - 1, 1) = intColumn
MsgBox "Inserted " & strColTitle & " value into: " & cboVersion.ListCount - 1 & " index"
End If
intColumn = intColumn + 1
Wend
End Sub
If the user selects the "<new>" option from the ComboBox and then clicks the Command Button (cmdNext), the current Userform (frmUser1) will be hidden, and then a second Userform (frmUser2) will be loaded, and then shown.
Private Sub cmdNext_Click()
Dim intColFinal As Integer
intColFinal = cboVersion.Value
If intColFinal = -1 Then ' If the user selects the <new> option the index is equal to -1
Me.Hide
Load frmUser2
frmUser2.Show
Unload Me
Else
' Do other stuff
End If
End Sub
The new Userform which is loaded and shown contains 2 labels (lblVersion & lblRev), 2 TextBoxes (txtVersionNum & txtRevNum) and 2 Command Buttons (cmdCreate & cmdCancel)
After the user types in a vaild Version number and Rev number, I go through and create a String (strFinalTitle) which will be the title of a new column which will be inserted into the current Worksheet.
My problem comes when trying to check if the String (strFinalTitle) is an accidental duplicate of a current column's title. I know I could just go through again and search all the column title again as I did before, but that seem kinda pointless since I already have the list within my ComboBox on frmUser1. Here is the function which I want to do to check if strFinalTitle is a duplicate or not.
Function checkForDup(strValue As String) As Boolean
' Passing in my strFinalTitle as a parameter (strValue)
Dim bResult As Boolean
bResult = True
' Check each entry in the ComboBox on frmUser1
For tIndex = 0 To frmUser1.cboVersion.ListCount - 1
If strValue = frmUser1.cboVersion.List(tIndex, 0) Then
MsgBox "You Entered a title which is already present"
bResult = False
Exit For
End If
Next tIndex
checkForDup = bResult
End Function
Everything appeared to work, but what I found out through testing, is I must not be accessing cboVersion, because if you run this multiple times without fully exiting Excel the cboVersion.ListCount will return 0 although I can confirm that multiple items are added and show up in the ComboBox drop down.
I have done a some looking on Google and I found suggestions to change my accessing statement to something like this
Forms!frmUser1!cboVersion.ListCount
' -OR -
Forms!frmUser1!cboVersion.List(tIndex, 0)
But every time I try to do that I will get an error message stating:
"Run-time error '424':
Object required "
I know it may also be possible to get this to work just by using a Public variable for the declaration of frmUser1, but I am not a huge fan of using Public variables unless absolutely needed, and I feel like there is another way to do this without Public variables, but then again a may be wrong in this case.
Sorry for the long post just wanted to be thorough with my explanation. Please let me know if you need any other information at all, and thank you in advance for any help you can provide.
First my apologize if this question was asked in another post, but I have not been able to find an answer in my search so far.
As the title of this thread implies, I am looking for help in how to access entries found in a combobox, which is located in one Userform, from another different Userform. Below is a more detailed explanation:
Program Being Used: Microsoft Excel 2010
Within an Excel Workbook I launch a macro which loads Userform (for ease lets just call it frmUser1) by declaring a variable within a Subroutine (Macro1) within a Module (Module1).
Sub Macro1()
Dim myForm As frmUser1
Set myForm = New frmUser1
myForm.Show
Unload myForm
End Sub
On Userform frmUser1, I have a label (lblPrompt), a ComboBox (cboVersion), and a Command Button (cmdNext)
The ComboBox is populated with a list of column titles for on a Worksheet within the Excel Workbook, and also the column index number (column A = 1, B = 2,...). There is also a "<new>" entry which is placed inside the ComboBox in case the user wants create a new version.
Dim intColumn As Integer
intColumn = 1
cboVersion.AddItem "<new>"
cboVersion.List(cboVersion.ListCount - 1, 1) = -1
While (intColumn < 10) ' I just initially do the first 10 columns for testing, right now.
Dim strColTitle As String
strColTitle = ActiveWorkbook.Worksheets("Versions").Cells(1, intColumn)
If ( 'Version numbers are in the correct format, and not a blank title' ) Then
cboVersion.AddItem
cboVersion.List(cboVersion.ListCount - 1, 0) = strColTitle
cboVersion.List(cboVersion.ListCount - 1, 1) = intColumn
MsgBox "Inserted " & strColTitle & " value into: " & cboVersion.ListCount - 1 & " index"
End If
intColumn = intColumn + 1
Wend
End Sub
If the user selects the "<new>" option from the ComboBox and then clicks the Command Button (cmdNext), the current Userform (frmUser1) will be hidden, and then a second Userform (frmUser2) will be loaded, and then shown.
Private Sub cmdNext_Click()
Dim intColFinal As Integer
intColFinal = cboVersion.Value
If intColFinal = -1 Then ' If the user selects the <new> option the index is equal to -1
Me.Hide
Load frmUser2
frmUser2.Show
Unload Me
Else
' Do other stuff
End If
End Sub
The new Userform which is loaded and shown contains 2 labels (lblVersion & lblRev), 2 TextBoxes (txtVersionNum & txtRevNum) and 2 Command Buttons (cmdCreate & cmdCancel)
After the user types in a vaild Version number and Rev number, I go through and create a String (strFinalTitle) which will be the title of a new column which will be inserted into the current Worksheet.
My problem comes when trying to check if the String (strFinalTitle) is an accidental duplicate of a current column's title. I know I could just go through again and search all the column title again as I did before, but that seem kinda pointless since I already have the list within my ComboBox on frmUser1. Here is the function which I want to do to check if strFinalTitle is a duplicate or not.
Function checkForDup(strValue As String) As Boolean
' Passing in my strFinalTitle as a parameter (strValue)
Dim bResult As Boolean
bResult = True
' Check each entry in the ComboBox on frmUser1
For tIndex = 0 To frmUser1.cboVersion.ListCount - 1
If strValue = frmUser1.cboVersion.List(tIndex, 0) Then
MsgBox "You Entered a title which is already present"
bResult = False
Exit For
End If
Next tIndex
checkForDup = bResult
End Function
Everything appeared to work, but what I found out through testing, is I must not be accessing cboVersion, because if you run this multiple times without fully exiting Excel the cboVersion.ListCount will return 0 although I can confirm that multiple items are added and show up in the ComboBox drop down.
I have done a some looking on Google and I found suggestions to change my accessing statement to something like this
Forms!frmUser1!cboVersion.ListCount
' -OR -
Forms!frmUser1!cboVersion.List(tIndex, 0)
But every time I try to do that I will get an error message stating:
"Run-time error '424':
Object required "
I know it may also be possible to get this to work just by using a Public variable for the declaration of frmUser1, but I am not a huge fan of using Public variables unless absolutely needed, and I feel like there is another way to do this without Public variables, but then again a may be wrong in this case.
Sorry for the long post just wanted to be thorough with my explanation. Please let me know if you need any other information at all, and thank you in advance for any help you can provide.