Dates changing format on combo change and textbox change

M

mrjackg

New Member
Joined
Aug 3, 2016
Messages
25


I am having issue of the date format changing each time to combobox change event is triggered as per the gif.

This is my code for the on change event
Code: 
Private Sub ComboBox1_Change()Dim r As Excel.Range


    Set r = Worksheets("Sheet7").Range("D10:CI10").Find(What:=ComboBox1.Value, LookAt:=xlPart, MatchCase:=False)


    If Not r Is Nothing Then
        TextBox1.Text = r.Offset(1, 0).Value
        TextBox2.Text = r.Offset(1, 1).Value
        TextBox3.Text = r.Offset(1, 2).Value
        TextBox4.Text = r.Offset(1, 3).Value
        TextBox5.Text = r.Offset(1, 4).Value
        TextBox6.Text = r.Offset(1, 5).Value
        TextBox7.Text = r.Offset(1, 6).Value


    End If
End Sub

And this is my code for the text box change event

Code: 
Private Sub TextBox1_Change()If ComboBox1.Value = "JAN" Then
Worksheets("Sheet7").Range("D11") = TextBox1.Value
ElseIf ComboBox1.Value = "FEB" Then
Worksheets("Sheet7").Range("K11") = TextBox1.Value
ElseIf ComboBox1.Value = "MAR" Then
Worksheets("Sheet7").Range("R11") = TextBox1.Value
ElseIf ComboBox1.Value = "APR" Then
Worksheets("Sheet7").Range("Y11") = TextBox1.Value
ElseIf ComboBox1.Value = "MAY" Then
Worksheets("Sheet7").Range("AF11") = TextBox1.Value
ElseIf ComboBox1.Value = "JUN" Then
Worksheets("Sheet7").Range("AM11") = TextBox1.Value
ElseIf ComboBox1.Value = "JUL" Then
Worksheets("Sheet7").Range("AT11") = TextBox1.Value
ElseIf ComboBox1.Value = "AUG" Then
Worksheets("Sheet7").Range("BA11") = TextBox1.Value
ElseIf ComboBox1.Value = "SEP" Then
Worksheets("Sheet7").Range("BH11") = TextBox1.Value
ElseIf ComboBox1.Value = "OCT" Then
Worksheets("Sheet7").Range("BO11") = TextBox1.Value
ElseIf ComboBox1.Value = "NOV" Then
Worksheets("Sheet7").Range("BV11") = TextBox1.Value
ElseIf ComboBox1.Value = "DEC" Then
Worksheets("Sheet7").Range("CC11") = TextBox1.Value
End If
End Sub
Private Sub TextBox2_Change()
If ComboBox1.Value = "JAN" Then
Worksheets("Sheet7").Range("E11") = TextBox2.Value
ElseIf ComboBox1.Value = "FEB" Then
Worksheets("Sheet7").Range("L11") = TextBox2.Value
ElseIf ComboBox1.Value = "MAR" Then
Worksheets("Sheet7").Range("S11") = TextBox2.Value
ElseIf ComboBox1.Value = "APR" Then
Worksheets("Sheet7").Range("Z11") = TextBox2.Value
ElseIf ComboBox1.Value = "MAY" Then
Worksheets("Sheet7").Range("AG11") = TextBox2.Value
ElseIf ComboBox1.Value = "JUN" Then
Worksheets("Sheet7").Range("AN11") = TextBox2.Value
ElseIf ComboBox1.Value = "JUL" Then
Worksheets("Sheet7").Range("AU11") = TextBox2.Value
ElseIf ComboBox1.Value = "AUG" Then
Worksheets("Sheet7").Range("BB11") = TextBox2.Value
ElseIf ComboBox1.Value = "SEP" Then
Worksheets("Sheet7").Range("BI11") = TextBox2.Value
ElseIf ComboBox1.Value = "OCT" Then
Worksheets("Sheet7").Range("BP11") = TextBox2.Value
ElseIf ComboBox1.Value = "NOV" Then
Worksheets("Sheet7").Range("BW11") = TextBox2.Value
ElseIf ComboBox1.Value = "DEC" Then
Worksheets("Sheet7").Range("CD11") = TextBox2.Value
End If
End Sub
Private Sub TextBox3_Change()
If ComboBox1.Value = "JAN" Then
Worksheets("Sheet7").Range("F11") = TextBox3.Value
ElseIf ComboBox1.Value = "FEB" Then
Worksheets("Sheet7").Range("M11") = TextBox3.Value
ElseIf ComboBox1.Value = "MAR" Then
Worksheets("Sheet7").Range("T11") = TextBox3.Value
ElseIf ComboBox1.Value = "APR" Then
Worksheets("Sheet7").Range("AA11") = TextBox3.Value
ElseIf ComboBox1.Value = "MAY" Then
Worksheets("Sheet7").Range("AH11") = TextBox3.Value
ElseIf ComboBox1.Value = "JUN" Then
Worksheets("Sheet7").Range("AO11") = TextBox3.Value
ElseIf ComboBox1.Value = "JUL" Then
Worksheets("Sheet7").Range("AV11") = TextBox3.Value
ElseIf ComboBox1.Value = "AUG" Then
Worksheets("Sheet7").Range("BC11") = TextBox3.Value
ElseIf ComboBox1.Value = "SEP" Then
Worksheets("Sheet7").Range("BJ11") = TextBox3.Value
ElseIf ComboBox1.Value = "OCT" Then
Worksheets("Sheet7").Range("BQ11") = TextBox3.Value
ElseIf ComboBox1.Value = "NOV" Then
Worksheets("Sheet7").Range("BX11") = TextBox3.Value
ElseIf ComboBox1.Value = "DEC" Then
Worksheets("Sheet7").Range("CE11") = TextBox3.Value
End If
End Sub
Private Sub TextBox4_Change()
If ComboBox1.Value = "JAN" Then
Worksheets("Sheet7").Range("G11") = TextBox4.Value
ElseIf ComboBox1.Value = "FEB" Then
Worksheets("Sheet7").Range("N11") = TextBox4.Value
ElseIf ComboBox1.Value = "MAR" Then
Worksheets("Sheet7").Range("U11") = TextBox4.Value
ElseIf ComboBox1.Value = "APR" Then
Worksheets("Sheet7").Range("AB11") = TextBox4.Value
ElseIf ComboBox1.Value = "MAY" Then
Worksheets("Sheet7").Range("AI11") = TextBox4.Value
ElseIf ComboBox1.Value = "JUN" Then
Worksheets("Sheet7").Range("AP11") = TextBox4.Value
ElseIf ComboBox1.Value = "JUL" Then
Worksheets("Sheet7").Range("AW11") = TextBox4.Value
ElseIf ComboBox1.Value = "AUG" Then
Worksheets("Sheet7").Range("BD11") = TextBox4.Value
ElseIf ComboBox1.Value = "SEP" Then
Worksheets("Sheet7").Range("BK11") = TextBox4.Value
ElseIf ComboBox1.Value = "OCT" Then
Worksheets("Sheet7").Range("BR11") = TextBox4.Value
ElseIf ComboBox1.Value = "NOV" Then
Worksheets("Sheet7").Range("BY11") = TextBox4.Value
ElseIf ComboBox1.Value = "DEC" Then
Worksheets("Sheet7").Range("CF11") = TextBox4.Value
End If
End Sub
Private Sub TextBox5_Change()
If ComboBox1.Value = "JAN" Then
Worksheets("Sheet7").Range("H11") = TextBox5.Value
ElseIf ComboBox1.Value = "FEB" Then
Worksheets("Sheet7").Range("O11") = TextBox5.Value
ElseIf ComboBox1.Value = "MAR" Then
Worksheets("Sheet7").Range("V11") = TextBox5.Value
ElseIf ComboBox1.Value = "APR" Then
Worksheets("Sheet7").Range("AC11") = TextBox5.Value
ElseIf ComboBox1.Value = "MAY" Then
Worksheets("Sheet7").Range("AJ11") = TextBox5.Value
ElseIf ComboBox1.Value = "JUN" Then
Worksheets("Sheet7").Range("AQ11") = TextBox5.Value
ElseIf ComboBox1.Value = "JUL" Then
Worksheets("Sheet7").Range("AX11") = TextBox5.Value
ElseIf ComboBox1.Value = "AUG" Then
Worksheets("Sheet7").Range("BE11") = TextBox5.Value
ElseIf ComboBox1.Value = "SEP" Then
Worksheets("Sheet7").Range("BL11") = TextBox5.Value
ElseIf ComboBox1.Value = "OCT" Then
Worksheets("Sheet7").Range("BS11") = TextBox5.Value
ElseIf ComboBox1.Value = "NOV" Then
Worksheets("Sheet7").Range("BZ11") = TextBox5.Value
ElseIf ComboBox1.Value = "DEC" Then
Worksheets("Sheet7").Range("CG11") = TextBox5.Value
End If
End Sub
Private Sub TextBox6_Change()
If ComboBox1.Value = "JAN" Then
Worksheets("Sheet7").Range("I11") = TextBox6.Value
ElseIf ComboBox1.Value = "FEB" Then
Worksheets("Sheet7").Range("P11") = TextBox6.Value
ElseIf ComboBox1.Value = "MAR" Then
Worksheets("Sheet7").Range("W11") = TextBox6.Value
ElseIf ComboBox1.Value = "APR" Then
Worksheets("Sheet7").Range("AD11") = TextBox6.Value
ElseIf ComboBox1.Value = "MAY" Then
Worksheets("Sheet7").Range("AK11") = TextBox6.Value
ElseIf ComboBox1.Value = "JUN" Then
Worksheets("Sheet7").Range("AR11") = TextBox6.Value
ElseIf ComboBox1.Value = "JUL" Then
Worksheets("Sheet7").Range("AY11") = TextBox6.Value
ElseIf ComboBox1.Value = "AUG" Then
Worksheets("Sheet7").Range("BF11") = TextBox6.Value
ElseIf ComboBox1.Value = "SEP" Then
Worksheets("Sheet7").Range("BM11") = TextBox6.Value
ElseIf ComboBox1.Value = "OCT" Then
Worksheets("Sheet7").Range("BT11") = TextBox6.Value
ElseIf ComboBox1.Value = "NOV" Then
Worksheets("Sheet7").Range("CA11") = TextBox6.Value
ElseIf ComboBox1.Value = "DEC" Then
Worksheets("Sheet7").Range("CH11") = TextBox6.Value
End If
End Sub
Private Sub TextBox7_Change()
If ComboBox1.Value = "JAN" Then
Worksheets("Sheet7").Range("J11") = TextBox7.Value
ElseIf ComboBox1.Value = "FEB" Then
Worksheets("Sheet7").Range("Q11") = TextBox7.Value
ElseIf ComboBox1.Value = "MAR" Then
Worksheets("Sheet7").Range("X11") = TextBox7.Value
ElseIf ComboBox1.Value = "APR" Then
Worksheets("Sheet7").Range("AE11") = TextBox7.Value
ElseIf ComboBox1.Value = "MAY" Then
Worksheets("Sheet7").Range("AL11") = TextBox7.Value
ElseIf ComboBox1.Value = "JUN" Then
Worksheets("Sheet7").Range("AS11") = TextBox7.Value
ElseIf ComboBox1.Value = "JUL" Then
Worksheets("Sheet7").Range("AZ11") = TextBox7.Value
ElseIf ComboBox1.Value = "AUG" Then
Worksheets("Sheet7").Range("BG11") = TextBox7.Value
ElseIf ComboBox1.Value = "SEP" Then
Worksheets("Sheet7").Range("BN11") = TextBox7.Value
ElseIf ComboBox1.Value = "OCT" Then
Worksheets("Sheet7").Range("BU11") = TextBox7.Value
ElseIf ComboBox1.Value = "NOV" Then
Worksheets("Sheet7").Range("CB11") = TextBox7.Value
ElseIf ComboBox1.Value = "DEC" Then
Worksheets("Sheet7").Range("CI11") = TextBox7.Value
End If
End Sub

Any help is much appreciated!!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
changes like this will help
Code: 
TextBox1.Text = Format(r.Offset(1, 0).Value, "mmm dd yyyy")
Or

Code: 
TextBox1.Text = r.Offset(1, 0).Text
 
Upvote 0
Hi,
Quick study of your code suggests to me that each time you change month in your combobox which loads values from ranges to TextBoxes – ALL those TextBox_Change events trigger & try immediately to write back to the worksheet.
If this is the problem you are experiencing or part of it, then what you need to do is turn those events off during combobox change - however, such a function for these events does not exist in VBA so will need to create your own control method.
Also, you should be able to eliminate all the repetitive code used in each of the textbox change events using a common code with bit of indexing.

Give following a try & see if it both works & does what you want:

Code: 
'Variables MUST sit at TOP of your Forms Code page.
Dim ws As Worksheet
Dim step As Integer
Dim EventsEnable As Boolean



Private Sub ComboBox1_Change()
    Dim i As Integer


    'turn change events off
    EventsEnable = False


    step = IIf(Me.ComboBox1.ListIndex = 0, 0, Me.ComboBox1.ListIndex * 7)


    For i = 1 To 7
        Me.Controls("TextBox" & i).Text = ws.Cells(11, 4 + step).Offset(0, i - 1).Text
    Next i


    'turn change events on
    EventsEnable = True


End Sub


Private Sub TextBox1_Change()
    AddDates Me.TextBox1
End Sub


Private Sub TextBox2_Change()
    AddDates Me.TextBox2
End Sub


Private Sub TextBox3_Change()
    AddDates Me.TextBox3
End Sub


Private Sub TextBox4_Change()
    AddDates Me.TextBox4
End Sub


Private Sub TextBox5_Change()
    AddDates Me.TextBox5
End Sub


Private Sub TextBox6_Change()
    AddDates Me.TextBox6
End Sub


Private Sub TextBox7_Change()
    AddDates Me.TextBox7
End Sub


Sub AddDates(ByVal Textbox As Object)
    Dim box As Integer
    If Me.ComboBox1.ListIndex = -1 Or Not EventsEnable Then Exit Sub
    box = Val(Mid(Textbox.Name, 8))
    ws.Cells(11, 3 + box + step).Value = Textbox.Text
End Sub


Private Sub UserForm_Initialize()
    Dim i As Integer
    Set ws = Worksheets("Sheet7")
    With Me.ComboBox1
            .RowSource = ""
        For i = 1 To 12
            .AddItem MonthName(i, True)
        Next i
    End With
End Sub

NOTE:
1 - the variables outside of procedures – these MUST sit at TOP of your forms code page & repeat- OUTSIDE of any procedure.
2 – code is UNTESTED & I have not fully studied all your code so indexing idea may go awry if you layout is not consistent.
3 – Date Format issue reading from Range to TextBox is resolved as has already been suggested, by using Range.Text property.

Hope Helpful

Dave
 
Upvote 0
dmt32,

To control user form events, one has to explicitly code to test EnableEvents variable.
Every event code should begin by testing the variable.

I prefer to use a DisableMyEvents variable (since the initial value of any boolean variable is False)

Code: 
Dim DisableMyEvents as Boolean

Private Sub ComboBox1_Change()
    Dim i As Integer

    [COLOR="#FF0000"]If DisableMyEvents Then Exit Sub[/COLOR]
    
    ' turn events off
    DisableMyEvents = True

    step = IIf(Me.ComboBox1.ListIndex = 0, 0, Me.ComboBox1.ListIndex * 7)
    For i = 1 To 7
        Me.Controls("TextBox" & i).Text = ws.Cells(11, 4 + step).Offset(0, i - 1).Text
    Next i

    'turn change events on
    DisableMyEvents = False
End Sub


Private Sub TextBox1_Change()
    [COLOR="#FF0000"]If DisableMyEvents Then Exit Sub[/COLOR]
    AddDates Me.TextBox1
End Sub


Private Sub TextBox2_Change()
   [COLOR="#FF0000"] If DisableMyEvents Then Exit Sub[/COLOR]
    AddDates Me.TextBox2
End Sub


Private Sub TextBox3_Change()
    [COLOR="#FF0000"] If DisableMyEvents Then Exit Sub[/COLOR]
    AddDates Me.TextBox3
End Sub

' etc

Sub AddDates(ByVal Textbox As Object)
    [COLOR="#FF0000"]Rem this is not event code, does not require a DisableMyEvents check[/COLOR]

    Dim box As Integer
    If Me.ComboBox1.ListIndex = -1 Or Not EventsEnable Then Exit Sub
    box = Val(Mid(Textbox.Name, 8))
    ws.Cells(11, 3 + box + step).Value = Textbox.Text
End Sub


'etc

Also, the OP code relies on the ComboBox > Textbox firing the Textbox Change event. It sounds like the OP wants "user changes ComboBox" to result in the worksheet being changed.
 
Last edited:
Upvote 0
Hi,
coding is a matter of style & everyone will have their own approach (right or wrong)

I prefer EnableEvents as my variable & also this IS tested for in common procedure AddDates - I do this to save all those If tests in the change events - may not be the most efficient approach or one that you would take but for such a short application, should be no big deal.

I m not fully clear what OP is seeking but just offered some suggestions that may or may not assist.

Dave
 
Last edited:
Upvote 0
Whether one goes with Enable or Disable, one has to test for it in the event code.
Also if one is using the Enable approach, EnableEvents has to be set to True in the Intialize event.

The Workbook.EnableEvents property does not effect user forms. There is no built in toggle for user form events.

One has to use code like
Code: 
If Not EnableEvents Then Exit Sub
in each control's event routines to achieve build in the ability to toggle user form events.
 
Upvote 0
with great respect, I fully aware everything you outlined & the named variable EventsEnable IS tested for each change event in the common code & exits procedure if set false. It may not be your way of doing things but as stated, all programmers have their own approach.

I thank you for your comments but solution is directed to the OP - if you have an alternative solution for OP to try then by all means post it as this is what the forum is about.

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
ComboBox List selection to trigger code
Replies
1
Views
223
MichaelRSnow
M
E
  • Solved
Userform as search tool
Replies
5
Views
258
Akuini
Akuini
A
  • Solved
make faster code after increase data for 4000 rows for each sheet on userform
2
Replies
13
Views
927
Akuini
Akuini
A
  • Question
possible calculation in listbox for debit , credit when search based on textbox ?
Replies
0
Views
286
abdelfattah
A
M
  • Question
drag and drop listbox that updates at worksheet
Replies
2
Views
607
monvarona
M

Forum statistics

Threads
1,214,950
Messages
6,122,428
Members
449,083
Latest member
Ava19

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