SetFocus command being ignored

jrooney7 · #1 03-27-2019, 05:58 AM

Hello all, I have a userform containing several textboxes and comboboxes. I have the below code that executes on the exit of one combobox and inserts text into a textbox in the userform while it is still active. I also have code that allows that textbox to grow as text is entered. Upon exiting the combobox, I am getting an error for the LineCount property (in the textbox growing code) stating that the textbox needs the focus. But the first line of the textbox growing code is the SetFocus command and it does not seem to be reading it. I also put the SetFocus command in the exit code for the combobox, thinking it was the link that was the problem. Here is my code for the ComboBox exit:

Code:

Private Sub FirearmTypeComboBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    ResultsTextBox.SetFocus
   
    If RestorationTypeComboBox.Value = "Successful Restoration" Then
        ResultsTextBox.Value = "Examination and [magnetic and/or chemical] processing of the aaa bbb restored the original obliterated serial number which was determined to be 'ccc'."
        ResultsTextBox.Text = Replace(ResultsTextBox, "aaa", ItemComboBox.Value)
        ResultsTextBox.Text = Replace(ResultsTextBox, "bbb", FirearmTypeComboBox.Value)
        ResultsTextBox.Text = Replace(ResultsTextBox, "ccc", RestoredCharactersTextBox.Value)
    End If
    
    If RestorationTypeComboBox.Value = "Partial Restoration" Then
        ResultsTextBox.Value = "Examination and [magnetic and/or chemical] processing of the aaa bbb partially restored the original obliterated serial number which was determined to be 'ccc'."
        ResultsTextBox.Text = Replace(ResultsTextBox, "aaa", ItemComboBox.Value)
        ResultsTextBox.Text = Replace(ResultsTextBox, "bbb", FirearmTypeComboBox.Value)
        ResultsTextBox.Text = Replace(ResultsTextBox, "ccc", RestoredCharactersTextBox.Value)
    End If
    
    If RestorationTypeComboBox.Value = "Unsuccessful Restoration" Then
        ResultsTextBox.Value = "Examination and [magnetic and/or chemical] processing of the aaa bbb failed to restore any part of the serial number."
        ResultsTextBox.Text = Replace(ResultsTextBox, "aaa", ItemComboBox.Value)
        ResultsTextBox.Text = Replace(ResultsTextBox, "bbb", FirearmTypeComboBox.Value)
    End If
    
    ResultsTextBox.SetFocus
    
End Sub

Here is the code for allowing the textbox to grow in the userform:

Code:

Private Sub ResultsTextBox_Change()
    
    ResultsTextBox.SetFocus
    'the line below is where the debug directs me
    If ResultsTextBox.LineCount > 1 Then
        ResultsTextBox.Height = ((ResultsTextBox.LineCount - 2) * 13.5) + 33
        With ResultsTextBox
            .SelStart = 0
            .SelStart = Len(.Text)
        End With
        
        SNRCommandButton.Top = ResultsTextBox.Top + ResultsTextBox.Height + 22.5
        SNRUserForm.ScrollHeight = SNRCommandButton.Top + SNRCommandButton.Height + 28
    End If
    
    If ResultsTextBox.LineCount = 1 Then
        ResultsTextBox.Height = 19.5
        SNRCommandButton.Top = ResultsTextBox.Top + ResultsTextBox.Height + 22.5
        SNRUserForm.ScrollHeight = SNRCommandButton.Top + SNRCommandButton.Height + 28
    End If

End Sub

If I remove the code for the exit of the combobox, the linecount code works fine and the textbox grows properly. It seems like it should work, and it has in the past, but for some reason I get the error every time now. Help!

gmayor · #2 03-27-2019, 06:51 AM

It works here based on the information provided. You would need to attach the document/template in order to establish what else is going on.

jrooney7 · #3 03-27-2019, 12:02 PM

Here is the docm and the supporting Excel file (to populate the dropdowns). The issue I'm having is with the SNRUserForm. If you download them to C:\Users\Public they should work together...

Guessed · #4 03-27-2019, 08:26 PM

I'm not sure why the Change is not working but it only appears to fail if you tab out of the FirearmTypeComboBox - clicking to other boxes is fine. In any case, your code was changing the contents of the ResultsTextBox multiple times which was calling the second macro multiple times. I've recoded that to be more efficient.

Try changing the ResultsTextBox code to _AfterUpdate instead of _Change

Code:

Private Sub FirearmTypeComboBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)    '
  Dim aCtl As Control, sText As String
  Set aCtl = Me.ResultsTextBox
  
  Select Case Me.RestorationTypeComboBox
    Case "Successful Restoration"
      sText = "Examination and [magnetic and/or chemical] processing of the aaa bbb restored the original obliterated serial number which was determined to be 'ccc'."
      sText = Replace(sText, "ccc", RestoredCharactersTextBox.Value)
    Case "Partial Restoration"
      sText = "Examination and [magnetic and/or chemical] processing of the aaa bbb partially restored the original obliterated serial number which was determined to be ccc."
      sText = Replace(sText, "ccc", RestoredCharactersTextBox.Value)
    Case "Unsuccessful Restoration"
      sText = "Examination and [magnetic and/or chemical] processing of the aaa bbb failed to restore any part of the serial number."
  End Select
  
  sText = Replace(sText, "aaa", ItemComboBox.Value)
  sText = Replace(sText, "bbb", FirearmTypeComboBox.Value)
  Me.ResultsTextBox = sText
End Sub

Private Sub ResultsTextBox_AfterUpdate()
  With Me.ResultsTextBox
    .SetFocus
    .SelStart = 0
    .SelLength = Len(.Text)
    If .LineCount > 1 Then
      .Height = ((.LineCount - 2) * 13.5) + 33
      SNRCommandButton.Top = .Top + .Height + 22.5
      SNRUserForm.ScrollHeight = SNRCommandButton.Top + SNRCommandButton.Height + 28
    Else
      .Height = 19.5
      SNRCommandButton.Top = .Top + .Height + 22.5
      SNRUserForm.ScrollHeight = SNRCommandButton.Top + SNRCommandButton.Height + 28
    End If
  End With
End Sub

jrooney7 · #5 03-28-2019, 07:13 AM

Thank you! I will apply these fixes to all my other userforms!

P.S. Love your avatar!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Show table list by using chek box command and save document on sharepoint by using command button	Zhibek	Word VBA	3	08-16-2018 06:19 PM
How do I use the Go To command?	Jbee	PowerPoint	3	12-06-2017 08:39 AM
Add Command to Bar	neiljsalkind	Excel	0	02-03-2012 08:31 AM
.setfocus not working why?	Cbrehm	Excel Programming	0	05-11-2011 02:52 AM
Add-In:How to add command right click command bar	phang	Outlook	0	01-15-2007 02:53 AM