[RESOLVED] Userform initialize (sometimes works, sometimes don't!)
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
Re: Userform initialize (sometimes works, sometimes don't!)
Is this a VB.NET question or VBA?
Re: Userform initialize (sometimes works, sometimes don't!)
Hello,
This is a VBA question, sorry if I posted on the wrong thread.
Re: Userform initialize (sometimes works, sometimes don't!)
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.
Re: Userform initialize (sometimes works, sometimes don't!)
Thank you so much!
I wish you a great weekend,
Pepito
Re: Userform initialize (sometimes works, sometimes don't!)
Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.
Re: Userform initialize (sometimes works, sometimes don't!)
try forcing the position of the userform to the second monitor, by setting the top or left as appriate
2 Attachment(s)
Re: Userform initialize (sometimes works, sometimes don't!)
Attachment 139837Attachment 139839
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
Re: Userform initialize (sometimes works, sometimes don't!)
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
Code:
Private Sub Workbook_Open()
Application.OnTime Now + TimeSerial(0, 0, 1), "openuserform"
End Sub
in a module
Code:
Public sub openuserform()
UserForm1.Show
end sub
1 Attachment(s)
Re: Userform initialize (sometimes works, sometimes don't!)
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.
Attachment 139841
Re: Userform initialize (sometimes works, sometimes don't!)
did you try setting the position of the userform by using it properties?
1 Attachment(s)
Re: Userform initialize (sometimes works, sometimes don't!)
Attachment 139843
This is what I have set on the userform.
Re: Userform initialize (sometimes works, sometimes don't!)
try moving the code from initialize to activate, if the form may be activated multiple times, use a static boolean
like
Code:
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
Re: Userform initialize (sometimes works, sometimes don't!)
I think my adrenaline has grow :)))
Thank you so much!
Pepito