Keeping track of last used ListBox - VBA

Question

I'm currently working with forms in VBA, and want to use one form to modify some values in another form. So I have Form1, which has three ListBoxes that hold a bunch of items each. Then I have Form2, which has a non-modifiable TextBox that will contain the value of the selected ListBox item that the user wants to edit.

However, since I have multiple ListBoxes, I want to know which ListBox I last clicked on, so that I can draw the selected item from that list box and edit that item when the user clicks "Apply" in Form2.

I've looked created a property that will keep track of the name of the last ListBox. Thing is, I'm having trouble using it. Here is my code:

Public Property Get LastClicked() As ListBox
    LastClicked = LastListBox
End Property

Public Property Let LastClicked(boxName As ListBox)
    LastListBox = CStr(boxName)
End Property

Private Sub FirstNameTextBox_Change()
    If (FirstNameTextBox.ListIndex <> -1) Then
        EditButton.Enabled = True
    Else
        EditButton.Enabled = False
    End If
End Sub

Private Sub FirstNameTextBox_Click()
    LastClicked (FirstNameTextBox)
End Sub

Private Sub LastNameTextBox_Click()
    LastClicked (LastNameTextBox)
End Sub

When I attempt to set the property with the name of the listbox, it brings back an error:

"Invalid use of property"

I assume this means i'm passing in the wrong value, but I don't know what other value to pass in. I'd appreciate any help I can get on this.

Comintern Comintern · Accepted Answer · 2015-05-07T23:17:06

2 problems. First, LastClicked is a property, not a method. That means you need to assign it a value, not pass it a parameter. Second, properties that expose Objects need to use Property Set instead of Property Let. Property Let is only for primatives. Try something like this:

Option Explicit

Private LastListBox As ListBox

Public Property Get LastClicked() As ListBox
    Set LastClicked = LastListBox
End Property

Public Property Set LastClicked(boxName As ListBox)
    Set LastListBox = boxName
End Property

Private Sub FirstNameTextBox_Change()
    If (FirstNameTextBox.ListIndex <> -1) Then
        EditButton.Enabled = True
    Else
        EditButton.Enabled = False
    End If
End Sub

Private Sub FirstNameTextBox_Click()
    Set LastClicked = FirstNameTextBox
End Sub

Private Sub LastNameTextBox_Click()
    Set LastClicked = LastNameTextBox
End Sub

Keeping track of last used ListBox - VBA

1 Answers