Solved: Access Combobox Entries found in a Userform from Another Userform

[lovemonkey16]

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.

[Aflatoon]

You could add a property (or simple public variable) to the second form that allows you to pass the combobox directly to it before you show it and then the function can refer to that combobox's List property directly.

[lovemonkey16]

Hello Aflatoon,

Thank you for the suggestion, and quick response. I will go through and try to implement your suggestion on using a property, and let you know how it goes.

[lovemonkey16]

Okay I got my issue fixed. Thanks again Aflatoon, your idea of using a property to pass the ComboBox worked great. Here is what I now have if anyone else in the future is wondering

frmUser1

Private Sub cmbNext_Click()

Dim intColFinal As Integer

intColFinal = cboVersion.Value

If intColFinal = -1 Then 'If the user selects the <new> option the index is set to -1

Me.Hide

Load frmUser2

frmUser2.setCombo = cboVersion 'Assign setCombo Property to cboVersion ComboBox for user later in frmUser2

frmUser2.Show

Unload Me

Else

' Do other stuff

End If

End Sub



frmUser2


Private oComboVersion As MSForms.ComboBox

Public Property Let setCombo(ByRef oComboRef As ComboBox)
Set oComboVersion = oComboRef
End Property



Thanks Aflatoon :thumb