[RESOLVED] Userform initialize (sometimes works, sometimes don't!)

[pepito_child]

Hello all,

I have a workbook saved as Excel Macro-Enabled Template and sometimes when I open the file, after the userform is initialize, the rules for the textbox aren't available (the vba tab order and the limitation in writing only numbers).
If I press tab is adding space in the cell, also if I press any button from the keyboard is added to the cell.
Most of the times I don't have any problems when the userform appears to be edited, but if the userform is having problems, my solution is to double click in the worksheet, then on the userform and after that it's working like there are no problems.
Could there be a problem in my initialization code, does the userform interferes with other excel files and needs some restrictions?
I'm using two monitors and when the userform works perfectly is when it appears on the second screen, if it appears in the first monitor, there are also the errors.

Private Sub Workbook_Open()
UserForm1.Show
End Sub

Private Sub UserForm_Initialize()
Me.ComboBox1.List = Array("BLOCK", "TWIN", "TRIPLE", "PAIR")
Me.OptionButton1.Value = True
ComboBox1.ListIndex = 0
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case Asc("0") To Asc("9")
Case Asc(".")
If InStr(1, Me.TextBox1.Text, ".") > 0 Then
KeyAscii = 0
End If
Case Else
KeyAscii = 0
End Select
End Sub

This are the settings for the text box object:
TabIndex - 1
TabKeyBehavior - False
TabStop - True
WordWrap - False

It's very frustrating because it's look like if I'm lucky, I can open the workbook with no errors.
Is there something that can be done for this userform to work fine?

Kind regards,
Pepito

[jmcilhinney]

Is this a VB.NET question or VBA?

[pepito_child]

Hello,

This is a VBA question, sorry if I posted on the wrong thread.

[jmcilhinney]

I shall ask the mods to move this thread to the Office forum, which is for VBA and other Office development. Please don't double-post.

[pepito_child]

Thank you so much!

I wish you a great weekend,
Pepito

[si_the_geek]

Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

[westconn1]

try forcing the position of the userform to the second monitor, by setting the top or left as appriate

[pepito_child]

Hello,

please find attached two captures from the excel workbook. In the first picture the userform is opening in monitor #1. When the userform is opening in this monitor, after I introduce the information in textbox 1 and press tab to move on the next textbox it's adding space into the textbox.
I believe it's something wrong into the activation code because if I double click into the worksheet and then on the userform then the tab order is working and I can move on every textbox.

Private Sub Workbook_Open()
UserForm1.Show
End Sub

Private Sub Userform_Activate()
AddToForm MIN_BOX
Me.ComboBox1.List = Array("BLOCK", "TWIN", "TRIPLE", "PAIR")
Me.OptionButton1.Value = True
ComboBox1.ListIndex = 0
End Sub


In the second capture is when the userform works perfectly, the situation when the userform automatically appears on the second monitor.

This situation is going me crazy because sometimes it's working sometimes not.

Kind regards,
Pepito

[westconn1]

you could try using application.ontime to see if it helps, sometimes code run during workbook_open can have problems, trying to process while the workbook is opening

Private Sub Workbook_Open()
  Application.OnTime Now + TimeSerial(0, 0, 1), "openuserform"
End Sub

in a module

Public sub openuserform()
  UserForm1.Show
end sub

[pepito_child]

Thank you for this alternative but the problems remains exactly the same. If I open the workbook sometimes it appears on the second monitor and works perfectly, if I reopen the workbook template and the userform appear on the first monitor I have the same errors.

[westconn1]

did you try setting the position of the userform by using it properties?

[pepito_child]

This is what I have set on the userform.

[westconn1]

try moving the code from initialize to activate, if the form may be activated multiple times, use a static boolean
like

Private Sub UserForm_Activate()
Static activated As Boolean
If Not activated Then
'do stuff here only the first time
activated = True
End If
' do other stuff here every time
End Sub

this probably is not required, if the userform is modal, but won't hurt anything

[pepito_child]

I think my adrenaline has grow ))

Thank you so much!
Pepito