Userform textbox launches another userform

F

FedElecQaEng

New Member
Joined
Sep 25, 2023
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
I have a userform with two textboxes for users to enter dates.

I have code that when the user clicks into one of the text boxes a second userform will appear.

My issue is the second userform only appears once for each textbox. When the main user form appears, and either text box is clicked the second user form will open. but after selecting an option on the second user form or closing the second user form, clicking the text boxes again does not relaunch the userform.

Below is the code I am using to launch the second userform
VBA Code: 
Private Sub ISSUE_DATE_enter()
EXTEND_OR_ISSUE.Caption = "ISSUE"
DATE_SELECT.Show
End Sub
Private Sub EXTEND_DATE_enter()
EXTEND_OR_ISSUE.Caption = "EXTEND"
DATE_SELECT.Show
End Sub

Note: The EXTEND_OR_ISSUE.Caption is a label that changes what the second userform does.

The first click works how I expect and closes how I expect however the second click does nothing.

Steps are summarized as follows:
  1. Launch the main userform
  2. click the issue date textbox
  3. The second user form opens
  4. click a date button on the second userform
  5. the second userform closes
  6. The selected date is put into the issue date textbox
  7. click the issue date textbox again - nothing happens
  8. click a different textbox and reclick the issue date text box - nothing happens
  9. delete the date in the issue date text box - nothing happens
  10. click a different textbox and reclick the issue date text box - nothing happens
  11. close main userform
  12. relaunch main userform
  13. click the issue date textbox
  14. The second user form opens
  15. click the X button on the top write of the second userform to close the second userform (Nothing is put into the issue date textbox
  16. reclick the issue date textbox and click a different textbox and reclick the issue date text box - nothing happens
The above steps are also true for the extend date text box.

For some reason, the second user does not launch a second time
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Since you're using the Enter event, it won't be triggered by clicking on the textbox unless that also changes focus from a different control to the textbox. I suspect your main problem is to do with how you are returning to the main form from the second form. A form should never unload itself, it should only hide and then the routine that loaded it should take care of unloading it. The will allow the calling code to actually finish when it should. If one form shows another and then the second form shows the first again, the initial code to show the second form has not actually completed because it stops when the second form is shown. The same applies to the code in the second form that shows the first one again - that routine doesn't actually end at that point as it's waiting for the first one to unload or hide. (If you view the call stack, you will see the procedures building up each time you load each form)

Do you have Unload statements in the second form?
 
Upvote 0
RoryA said:
Since you're using the Enter event, it won't be triggered by clicking on the textbox unless that also changes focus from a different control to the textbox. I suspect your main problem is to do with how you are returning to the main form from the second form. A form should never unload itself, it should only hide and then the routine that loaded it should take care of unloading it. The will allow the calling code to actually finish when it should. If one form shows another and then the second form shows the first again, the initial code to show the second form has not actually completed because it stops when the second form is shown. The same applies to the code in the second form that shows the first one again - that routine doesn't actually end at that point as it's waiting for the first one to unload or hide. (If you view the call stack, you will see the procedures building up each time you load each form)

Do you have Unload statements in the second form?
Click to expand...
Hello RoryA,

I do make use of the Unload Me statement in the second form. I did try changing it to DATE_SELECT.hide however the same thing is happening where after the first time opening the second form it does not open again.

Would there be a way to close out of the second form aside from unloading or hiding?

After the .show command in the first form, should I then put the unload command for the second form?

Also does clicking into a textbox not change focus?
 
Upvote 0
FedElecQaEng said:
Also does clicking into a textbox not change focus?
Click to expand...
Not if the focus is already in it.

Essentially the code in the first form should do something like:

VBA Code: 
Private Sub ISSUE_DATE_enter()
EXTEND_OR_ISSUE.Caption = "ISSUE"
Dim frm as DATE_SELECT
set frm = New DATE_SELECT
frm.Show
' read any properties of the form you want here, then unload it
Unload frm
End Sub

and the second form should use Me.Hide instead of any Unload statements.
 
Upvote 0
RoryA said:
Not if the focus is already in it.

Essentially the code in the first form should do something like:

VBA Code: 
Private Sub ISSUE_DATE_enter()
EXTEND_OR_ISSUE.Caption = "ISSUE"
Dim frm as DATE_SELECT
set frm = New DATE_SELECT
frm.Show
' read any properties of the form you want here, then unload it
Unload frm
End Sub

and the second form should use Me.Hide instead of any Unload statements.
Click to expand...
Still does not work for me.

It does not reopen the second form when I click the textbox after the form appears once
 
Upvote 0
Can you post the workbook somewhere (e.g. onedrive) for me to have a look at? No data necessary, just need to see the forms and code as they are.
 
Upvote 0
RoryA said:
Can you post the workbook somewhere (e.g. onedrive) for me to have a look at? No data necessary, just need to see the forms and code as they are.
Click to expand...
Sorry, I am unable to post this file online since it is owned by my company, I am just maintaining it and trying to improve some of the aspects.

I did try and comment out all the code for the second form and found that it acts the same way in that if the second form gets launched once it will not get launched a second time.

To me, it seems that there is no "re-entering" the textbox which is why the command only runs once. Is there a way to "re-enter" the textbox
 
Upvote 0
If you select another control and then select the original control again, that will trigger the Enter event again.

Can you post all the code from the second form?
 
Upvote 0
RoryA said:
If you select another control and then select the original control again, that will trigger the Enter event again.

Can you post all the code from the second form?
Click to expand...
The first part looks to be not happening. When I select another control and reselect the issue date control, the Enter event is not retriggering

Here is my code for the second form. Please note the second form is a makeshift date selector that shows the month with each button as a day in the month

VBA Code: 
Option Explicit
Dim CURRENT_MONTH_NUM As Integer
Dim CRRNT_MNTH_STRNG As String
Dim CURRENT_YEAR_NUM As Integer


Private Sub MONTH_DISPLAY_Click()
MONTH_RETURN_REF = CURRENT_MONTH_NUM
YEAR_RETURN_REF = CURRENT_YEAR_NUM
MONTH_SELECT_BOX.Show
CURRENT_MONTH_NUM = MONTH_RETURN_REF
CURRENT_YEAR_NUM = YEAR_RETURN_REF
Call USER_BUTTON_LOOP(MONTH_RETURN_REF, YEAR_RETURN_REF)
End Sub

Private Sub Userform_initialize()
With DATE_SELECT
.StartUpPosition = 0

.Width = 230
.Height = 267
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
End With
CURRENT_MONTH_NUM = Month(Now())
CURRENT_YEAR_NUM = Year(Now())
Call USER_BUTTON_LOOP(CURRENT_MONTH_NUM, CURRENT_YEAR_NUM)
End Sub
Private Sub CANCEL_BUTTON_Click()
Unload Me
End Sub
Private Sub NEXT_MONTH_BTTN_Click()
CURRENT_MONTH_NUM = CURRENT_MONTH_NUM + 1
If CURRENT_MONTH_NUM > 12 Then
    CURRENT_MONTH_NUM = 1
    CURRENT_YEAR_NUM = CURRENT_YEAR_NUM + 1
End If
Call USER_BUTTON_LOOP(CURRENT_MONTH_NUM, CURRENT_YEAR_NUM)
End Sub
Private Sub PREV_MONTH_BTTN_Click()
CURRENT_MONTH_NUM = CURRENT_MONTH_NUM - 1
If CURRENT_MONTH_NUM = 0 Then
    CURRENT_MONTH_NUM = 12
    CURRENT_YEAR_NUM = CURRENT_YEAR_NUM - 1
End If
Call USER_BUTTON_LOOP(CURRENT_MONTH_NUM, CURRENT_YEAR_NUM)
End Sub
Private Sub TODAY_BUTTON_CLICK()
CURRENT_MONTH_NUM = Month(Now())
CURRENT_YEAR_NUM = Year(Now())
Call USER_BUTTON_LOOP(CURRENT_MONTH_NUM, CURRENT_YEAR_NUM)
End Sub
Private Sub USER_BUTTON_LOOP(MONTH_REF As Integer, YEAR_REF As Integer)
Dim BUTTON_NUM As Integer
For BUTTON_NUM = 1 To 42
    Controls("DAY_BTTN_" & BUTTON_NUM).Caption = ""
Next BUTTON_NUM
'-------------------------
Dim s1 As Long
Dim s2 As Long
Dim n As Integer
s1 = DateSerial(YEAR_REF, MONTH_REF, 1)
s2 = DateSerial(YEAR_REF, MONTH_REF + 1, 1)
n = s2 - s1
Dim DAY_LOOP As Integer
Select Case Weekday(s1)
    Case 1 'Firt Day Sunday
        For DAY_LOOP = 1 To n
            Controls("DAY_BTTN_" & DAY_LOOP).Caption = DAY_LOOP
        Next DAY_LOOP
    Case 2
        For DAY_LOOP = 1 To n
            Controls("DAY_BTTN_" & DAY_LOOP + 1).Caption = DAY_LOOP
        Next DAY_LOOP
    Case 3
        For DAY_LOOP = 1 To n
            Controls("DAY_BTTN_" & DAY_LOOP + 2).Caption = DAY_LOOP
        Next DAY_LOOP
    Case 4
        For DAY_LOOP = 1 To n
            Controls("DAY_BTTN_" & DAY_LOOP + 3).Caption = DAY_LOOP
        Next DAY_LOOP
    Case 5
        For DAY_LOOP = 1 To n
            Controls("DAY_BTTN_" & DAY_LOOP + 4).Caption = DAY_LOOP
        Next DAY_LOOP
    Case 6
        For DAY_LOOP = 1 To n
            Controls("DAY_BTTN_" & DAY_LOOP + 5).Caption = DAY_LOOP
        Next DAY_LOOP
    Case 7
        For DAY_LOOP = 1 To n
            Controls("DAY_BTTN_" & DAY_LOOP + 6).Caption = DAY_LOOP
        Next DAY_LOOP
End Select
MONTH_DISPLAY.Caption = MonthName(MONTH_REF) & " " & YEAR_REF
'-----------------------------------------------------------------
For BUTTON_NUM = 1 To 42
    If CStr(Controls("DAY_BTTN_" & BUTTON_NUM).Caption) = CStr(Day(Now())) And MONTH_REF = Month(Now()) And YEAR_REF = Year(Now()) Then
        Controls("DAY_BTTN_" & BUTTON_NUM).BackColor = &H80FFFF
    Else
        Controls("DAY_BTTN_" & BUTTON_NUM).BackColor = &H8000000F
    End If
    If Controls("DAY_BTTN_" & BUTTON_NUM).Caption = vbNullString Then
        Controls("DAY_BTTN_" & BUTTON_NUM).Visible = False
    Else
        Controls("DAY_BTTN_" & BUTTON_NUM).Visible = True
    End If
Next BUTTON_NUM
End Sub
'==========================================
Private Sub SELECTED_DAY(CONTROL_BUTTON As String)
'MsgBox (Controls(CONTROL_BUTTON).Caption)
If Controls(CONTROL_BUTTON).Caption <> "" Then
    Select Case ADD_SCAR.EXTEND_OR_ISSUE.Caption
        Case "EXTEND"
            ADD_SCAR.EXTEND_DATE.Value = DateSerial(CURRENT_YEAR_NUM, CURRENT_MONTH_NUM, DATE_SELECT.Controls(CONTROL_BUTTON).Caption)
        Case "ISSUE"
            ADD_SCAR.ISSUE_DATE.Value = DateSerial(CURRENT_YEAR_NUM, CURRENT_MONTH_NUM, DATE_SELECT.Controls(CONTROL_BUTTON).Caption)
    End Select
    Me.Hide
End If
End Sub
Private Sub DAY_BTTN_1_Click()
    Call SELECTED_DAY("DAY_BTTN_1")
End Sub
Private Sub DAY_BTTN_2_Click()
    Call SELECTED_DAY("DAY_BTTN_2")
End Sub
Private Sub DAY_BTTN_3_Click()
    Call SELECTED_DAY("DAY_BTTN_3")
End Sub
Private Sub DAY_BTTN_4_Click()
    Call SELECTED_DAY("DAY_BTTN_4")
End Sub
Private Sub DAY_BTTN_5_Click()
    Call SELECTED_DAY("DAY_BTTN_5")
End Sub
Private Sub DAY_BTTN_6_Click()
    Call SELECTED_DAY("DAY_BTTN_6")
End Sub
Private Sub DAY_BTTN_7_Click()
    Call SELECTED_DAY("DAY_BTTN_7")
End Sub
Private Sub DAY_BTTN_8_Click()
    Call SELECTED_DAY("DAY_BTTN_8")
End Sub
Private Sub DAY_BTTN_9_Click()
    Call SELECTED_DAY("DAY_BTTN_9")
End Sub
Private Sub DAY_BTTN_10_Click()
    Call SELECTED_DAY("DAY_BTTN_10")
End Sub
Private Sub DAY_BTTN_11_Click()
    Call SELECTED_DAY("DAY_BTTN_11")
End Sub
Private Sub DAY_BTTN_12_Click()
    Call SELECTED_DAY("DAY_BTTN_12")
End Sub
Private Sub DAY_BTTN_13_Click()
    Call SELECTED_DAY("DAY_BTTN_13")
End Sub
Private Sub DAY_BTTN_14_Click()
    Call SELECTED_DAY("DAY_BTTN_14")
End Sub
Private Sub DAY_BTTN_15_Click()
    Call SELECTED_DAY("DAY_BTTN_15")
End Sub
Private Sub DAY_BTTN_16_Click()
    Call SELECTED_DAY("DAY_BTTN_16")
End Sub
Private Sub DAY_BTTN_17_Click()
    Call SELECTED_DAY("DAY_BTTN_17")
End Sub
Private Sub DAY_BTTN_18_Click()
    Call SELECTED_DAY("DAY_BTTN_18")
End Sub
Private Sub DAY_BTTN_19_Click()
    Call SELECTED_DAY("DAY_BTTN_19")
End Sub
Private Sub DAY_BTTN_20_Click()
    Call SELECTED_DAY("DAY_BTTN_20")
End Sub
Private Sub DAY_BTTN_21_Click()
    Call SELECTED_DAY("DAY_BTTN_21")
End Sub
Private Sub DAY_BTTN_22_Click()
    Call SELECTED_DAY("DAY_BTTN_22")
End Sub
Private Sub DAY_BTTN_23_Click()
    Call SELECTED_DAY("DAY_BTTN_23")
End Sub
Private Sub DAY_BTTN_24_Click()
    Call SELECTED_DAY("DAY_BTTN_24")
End Sub
Private Sub DAY_BTTN_25_Click()
    Call SELECTED_DAY("DAY_BTTN_25")
End Sub
Private Sub DAY_BTTN_26_Click()
    Call SELECTED_DAY("DAY_BTTN_26")
End Sub
Private Sub DAY_BTTN_27_Click()
    Call SELECTED_DAY("DAY_BTTN_27")
End Sub
Private Sub DAY_BTTN_28_Click()
    Call SELECTED_DAY("DAY_BTTN_28")
End Sub
Private Sub DAY_BTTN_29_Click()
    Call SELECTED_DAY("DAY_BTTN_29")
End Sub
Private Sub DAY_BTTN_30_Click()
    Call SELECTED_DAY("DAY_BTTN_30")
End Sub
Private Sub DAY_BTTN_31_Click()
    Call SELECTED_DAY("DAY_BTTN_31")
End Sub
Private Sub DAY_BTTN_32_Click()
    Call SELECTED_DAY("DAY_BTTN_32")
End Sub
Private Sub DAY_BTTN_33_Click()
    Call SELECTED_DAY("DAY_BTTN_33")
End Sub
Private Sub DAY_BTTN_34_Click()
    Call SELECTED_DAY("DAY_BTTN_34")
End Sub
Private Sub DAY_BTTN_35_Click()
    Call SELECTED_DAY("DAY_BTTN_35")
End Sub
Private Sub DAY_BTTN_36_Click()
    Call SELECTED_DAY("DAY_BTTN_36")
End Sub
Private Sub DAY_BTTN_37_Click()
    Call SELECTED_DAY("DAY_BTTN_37")
End Sub
Private Sub DAY_BTTN_38_Click()
    Call SELECTED_DAY("DAY_BTTN_38")
End Sub
Private Sub DAY_BTTN_39_Click()
    Call SELECTED_DAY("DAY_BTTN_39")
End Sub
Private Sub DAY_BTTN_40_Click()
    Call SELECTED_DAY("DAY_BTTN_40")
End Sub
Private Sub DAY_BTTN_41_Click()
    Call SELECTED_DAY("DAY_BTTN_41")
End Sub
Private Sub DAY_BTTN_42_Click()
    Call SELECTED_DAY("DAY_BTTN_42")
End Sub

'-----------------------------

However, based on things I have done it does not appear to be due to the second form. I commented out all the code and tried relaunching and the same thing happened where the Enter event is not triggering a second time.

I just tried putting a message box that says hello in the code:
VBA Code: 
Private Sub ISSUE_DATE_enter()
EXTEND_OR_ISSUE.Caption = "ISSUE"
MsgBox ("HELLO")
DATE_SELECT.Show

End Sub
When I first select ISSUE_DATE the message appears and when I click OK the second form appears. Then when I close out of the second form, click to another control, and click back into the ISSUE_DATE textbox, nothing happens, i do not get a second "HELLO" message
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
populating a textbox on a userform from selections that are made from a listbox that is one another userform (popup)...
Replies
4
Views
200
kbishop94
K
T
  • Question
UserForm Close Event Hangs
Replies
0
Views
154
trough
T
S
  • Solved
userform command button, list box, and check box macro
Replies
7
Views
336
dmt32
D
P
  • Solved
Userform textbox format when populated by listbox
Replies
9
Views
381
dmt32
D
P
  • Question
Send UserForm value to another UserFrom
Replies
5
Views
247
RoryA
RoryA

Forum statistics

Threads
1,215,073
Messages
6,122,976
Members
449,095
Latest member
Mr Hughes

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top