Inputbox loses mouse

[huiettcm]

Good afternoon! My user form has a couple of instances where I use Application.InputBox to update some of the comboboxes. When the input box comes up the userform looses the mouse control. I tried disablemousescroll before the input box and enable after, but I still loose the scroll.

for example, i have comboboxes to standardize units of measure. There's an 'Other' option that brings up an inputbox for user input. If this isn't enough code to recreate, I can send the file, but I feel like it'd be a lot to go through.

Option Explicit

Public Sub UserForm_Initialize()

'populates unit dropdown boxes
If IsArrayEmpty(unts_input) Then
    unts_input = unts_array
End If

If unts_input(UBound(unts_input)) = "" Then
    ReDim Preserve unts_input(0 To UBound(unts_input) - 1)
End If

For Each ctrl In Me.Controls
    If TypeName(ctrl) = "TextBox" Then
        ctrl.value = ""
    End If

    If Right(ctrl.Name, 8) = "Units_cb" Then
        With ctrl
            .value = ""
            .Clear
            For i = 0 To UBound(unts_input)
                .AddItem unts_input(i)
            Next i
        End With
    End If
Next ctrl

EnableMouseScroll Me
end sub

Private Sub Thresh_Units_cb_Change()

If Me.Thresh_Units_cb.value = "Other" Then
    Me.Thresh_Units_cb.value = OtherUnits(Me.Thresh_Units_cb)
End If

End Sub

Private Function OtherUnits(ByVal ctrl As Control) As String

Dim ui As String
Dim i As Variant
Dim arybool As Boolean

DisableMouseScroll Me
ui = Application.InputBox("Enter Threshold/Objective Units for " & ctrl.value, Type:=2)

'catches a cancel to user input
If ui = "" Or ui = vbNullString Or ui = "False" Then

    'sets focus in the control
    DE_Form.Controls(ctrl.Name).SetFocus
    GoTo bottom
Else
'scans array for duplicate entry, adds user entry into dropdown array
    If IsArrayEmpty(unts_input) Then
        unts_input = unts_array
    End If

    For i = 0 To UBound(unts_input)
        If UCase(ui) = UCase(unts_input(i)) Then
            arybool = True
            Exit For
        Else
            arybool = False
        End If
    Next i

    'if not match in array, adds in the end of the array
    If arybool = False Then
        ReDim Preserve unts_input(0 To UBound(unts_input) + 1)
        unts_input(UBound(unts_input)) = ui

        'adds units to each dropdown box
        For Each i In DE_Form.Controls
            If Right(i.Name, 8) = "Units_cb" Then
                With DE_Form.Controls(i.Name)
                    .AddItem unts_input(UBound(unts_input))
                End With
            End If
        Next i
    End If
End If

OtherUnits = ui

bottom:

Call Me.GetUserForm
ctrl.SetFocus
EnableMouseScroll Me
End Function

Public Function unts_array() As Variant

unts_array = Array("Hours", "Days", "Barrels", "Miles", "NM", "Units", "J/Hr", "Gal/Hr", "Joules", "Gal", "kW", "kW/hr", "J/Sec", "Gal/Mile", "Miles/Gal", "Dollars", "Percent", "Other")

End Function

Public unts_input() As Variant

[cristianbuse]

Hi @huiettcm ,

Your problem is that you are using Application.InputBox which is different from VBA.InputBox.

I've just added an InputBox (same as VBA.InputBox) to the Demo file. There is a button with the same name once the demo is run. This one works just fine.

Since you are using Application.InputBox with Type:=2 which is String anyway, you can basically replace the line:

ui = Application.InputBox("Enter Threshold/Objective Units for " & ctrl.value, Type:=2)

with:

ui = InputBox("Enter Threshold/Objective Units for " & ctrl.value)

and your problem is solved. Also, there is no need to call DisableMouseScroll Me before or EnableMouseScroll Me after.

However, if you are planning to use the Application.InputBox with other types or even if you really need to use it anyway then the problem is that Application.InputBox makes the userform modeless.

As per the Notes section, this repository does not work with modeless forms. The code I wrote specifically disconnects the mouse hook to prevent a crash when the form is not modal. Even after the Application.InputBox is closed, the form is still modeless and you can test by being able to click cells in worksheets.

So, when using Application.InputBox, the form must be made modal back again. This can be achieved by hiding and then re-showing the form with .Show vbModal or just .Show if the form's ShowModal property is set to True.

[huiettcm]

I didn't knw that '''vba.inputbox''' (or more simply '''inputbox'''...) was an option and different than applicaiton.. It's much easier to just use the other input rather than reshow the form. Seems like an extra processing step that's really unnecessary. I also deleted the disable/enable lines. that was an misguided attempt to get around the wrong input boxes.

Thank you so much!