UserForm Update/TextBox DblClick

[Olwa]

Hi there,

im currently using Excel 2013 and i got 2 problems with my code:

1. Updating UserForm
Lets say I got a ComboBox in which u can choose a certain circle of friends (eg school, university, job, sports). After that you can choose a name within a ListBox. Depending on the name certain criteria (just Labels, eg male?) are marked with "x" or "" using TextBoxes for Yes and No
(eg tobi -> male? [x] Yes [ ] No, Tina -> male? [ ] Yes [x] No) depending on the entry in a sheet ive set before. Now this works back and forth, thus allowing me to change criteria (eg gender change). Additionaly i got a CommandButton allowing me to set new entries in the last row and setting "x" and "" whereever it is needed. The sheet now show the correct data, the UserForm however does not show a new circle of friends that is actually in the sheet by now. If i restart however it shows in the ComboBox. Any suggestions? Im initially not going to post parts of the code as everything works fine and im only looking for additional functionality.

2. TextBox DblClick
As mentioned above i got 2 TextBoxes to fill with an "x" or not ( i did not use ControlBoxes as there does not seem to been any way to alter the size of the actual Box). As its "too much work" to type an "x" i would like it to be shown as i double blick the box. This does not seem to work though.. Any suggestions in this matter? Wouldnt mind trying a completely different approach either. Heres the code:

Private Sub TextBox1_DblClick()
 Dim lindex As Long
    If ListBox1.ListIndex >= 0 Then
        lindex = 2
        Do While Trim(CStr(Sheets(1).Cells(lindex, 1).Value)) <> ""
            If ListBox1.Text = Trim(CStr(Sheets(1).Cells(lindex, 3).Value)) Then
                Sheets(1).Cells(lindex, 4).Value = "x"
        Exit Do
            End If
        lindex = lindex + 1
        Loop
    End If

 End Sub

Thank you guys in advance!

[mdmackillop]

Getting rid of the verbiage

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
                Cancel = True
                Sheets(1).Cells(10, 4).Value = "x"
End Sub

[Olwa]

So how is your code going to determine which "Name" and thus which cell i want an "x" in? Its not always row 10 :P

[mdmackillop]

Your code seems to fail because the sub line is wrong. I can't test the rest without mocking up a scenario which I'm not inclined to do.

[Olwa]

I see, its working now. Thank you. However the "x" is only showing in the cell, not in the TextBox. Any suggestions about that matter?

[mdmackillop]

Populate both and avoid the double click

Private Sub TextBox1_Change()
If UCase(TextBox1.Text) = "X" Then
Sheets(1).Cells(10, 4).Value = "x"
Else
Sheets(1).Cells(10, 4).ClearContents
End If
End Sub

[Olwa]

As i mentioned in the beginning, the purpose of my inquiry was to use the mouse only (as CheckBoxes are too small for my intentions), which makes me not want to avoid the double click. I might be getting you wrong though? Thank you either way.

[Olwa]

Not a beautiful solution but it does work if I simply add

If Sheets(1).Cells(lindex, 4).Value = "x" Then
TextBox1 = "x"
End If

Do you happen to know a solution for my first question as well? I might need to code a sample i guess as im not a native speaker and my problem might not be understandable.

[mdmackillop]

If i restart however it shows in the ComboBox.

I'm guessing you're wanting to update your form data as you make changes. Can you call your Initialize code from some other event?

[Olwa]

Does indeed work, the ComboBoxes are updated with the new entry. Minor issue: The Listbox now shows the new entry below the ones that already are in the listbox although they are in another cateroy of the ComboBox. Changing the ComboBox back and forth fixes it. Guess i gotta live with that? Thanks anyways, you helped me a lot.

[mdmackillop]

Clear the ListBox before repopulating it.

[Olwa]

Did the job, thank you once again.