Userform showing when not wanted, not called for

Question

im having some weird things happen with some code in excel

Private Sub CommandButton2_Click()

Dim thiswb As Workbook
Set thiswb = ActiveWorkbook


Call EnterDataToSS(thiswb)
Me.Hide
BeamNoFind.Show vbModal



End Sub

the called code basically drops some values into a spreadsheet. any left over values are populated to LISTBOX1 on BeamNoFind Userform.

Then on the BeamNoFind userform there is a button and the LISTBOX1. when you select and item from listbox1, and click the button, a third userform opens (VBMODELESS) to allow placement of the value.

below is the code of the button to show the third userform.

Private Sub CommandButton2_Click()
Dim Selected_Length As String
Dim Selected_Name As String

Dim Selected_Length_index As Integer

Selected_Length_index = BeamNoFind.ListBox1.ListIndex

With BeamNoFind.ListBox1

If .ListIndex > -1 Then

    Selected_Name = .Column(0, .ListIndex)
    Selected_Length = .Column(1, .ListIndex)

    CellInputForm.beam_length_label.Caption = Selected_Name & " [ " & Selected_Length & " ] "

    BeamNoFind.Hide
    'ChngDataSrc.Hide
    'Unload ChngDataSrc
    CellInputForm.Show vbModeless



Else
    MsgBox "No selection", vbExclamation, "Oops!"

End If
End With

End Sub

the weird thing is, when i click the button to show my modeless userform, to place the data in a cell, the initial macro is being triggered that drops you into the first userform. Where i have the commented code 'ChngDataSrc.Hide' and 'Unload ChngDataSrc' are my attempts to stop the userform from displaying when i dont want it to. When i unload the form, i then get an error instead of the form displaying, the error is with the initial macro:

Sub get_scheduling_data(control As IRibbonControl)
ChngDataSrc.Show

End Sub

It has something to do with vbModeless because if i replace "vbModeless" from "CellInputForm.Show vbModeless" line with "vbModal", it shows correctly, without the unwanted form (ChngDataSrc). but then the function of the form (select cell, press ok button, value placed in selected cell) is gone.

score 0 · Answer 1 · answered Jul 03 '14 at 06:02

I found a solution, but its not a real solution, i placed

ChngDataSrc.Hide in the Activate sub of the CellInputForm userform. So when CellInputForm.show vbModeless is run, and the ChngDataSrc userform pops up unwantedly, it is then hidden again.

Id rather find out why it is being showed in the first place, but this fix seems to work for now.

Userform showing when not wanted, not called for

1 Answers1