Update cell after update from userform

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,134
this is brill thank you, its amazing, I can put all the textboxes into one? wow that would be amazing as it is massive at the moment
Ok will do this in couple of steps

Follow all instructions CAREFULLY

1 - Make a BACKUP

2 - From the VB Editor Insert > Class Module

3 - Name the new module TextBoxClass <<< This is very important


Insert the following code in the Class Module

Code:
Public WithEvents TextBoxClass As MSForms.TextBox


Private Sub TextBoxClass_Change()
    With TextBoxClass
    Select Case .Value
        Case 1 To 2:
            .BackColor = vbYellow
        Case 3 To 20:
            .BackColor = vbGreen
        Case 0:
            .BackColor = vbRed
        Case Else:
            .BackColor = vbWhite
    End Select
    End With
End Sub
Ensure you copy ALL the codes as published.


Save the workbook & let me know - will then provide updated code for userform

Dave
 
Last edited:

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
925
Hi I did that but it hasn't done anything I deleted one of the colour codes for a textbox but it stayed white, I followed the process above, apart from what did you mean copy ALL the codes as published?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,134
Hi I did that but it hasn't done anything I deleted one of the colour codes for a textbox but it stayed white, I followed the process above, apart from what did you mean copy ALL the codes as published?
Without wishing to sound rude, really need to read all that I am posting

Save the workbook & let me know - will then provide updated code for userform
apart from what did you mean copy ALL the codes as published?
Exactly that - did you copy ALL the code I posted to the Class Module?

If so, confirm & I will provide the updated userform code for next step

Dave
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,134
Hi Dave, yes I have done that :)
follow carefully

1 - BackUP your workbook

2 - DELETE ALL THE CODE in your userforms code page

3 - Place all following code in your userforms code page


Code:
Dim TextBox() As New TextBoxClass
Dim wRow As Long, wCol As Long
Dim wsData As Worksheet


Private Sub TextBox54_Change()
    Dim Availability As Integer, Book As Integer
    
    If Not Me.Visible Or Len(Me.TextBox54) = 0 Then Exit Sub
    
    Availability = Val(Me.TextBox53.Value)
    Book = Val(Me.TextBox54.Value)
    
    If Availability > 0 Then
        If Book > 0 And Book <= Availability Then
            Availability = Availability - Book
            wsData.Cells(wRow, wCol).Value = Availability
            Me.TextBox53.Text = Availability


'un comment this line if you want to clear textbox
'after enter
            'Me.TextBox54.Text = ""


            RefreshTable
        End If
    End If
End Sub


Sub RefreshTable()
    Dim r As Long, c As Long
    Dim txtbox As Integer
    r = 2
    c = 2
    For txtbox = 34 To 122
     Select Case txtbox
     Case 51 To 54
     
     Case Else
        Me.Controls("TextBox" & txtbox).Text = wsData.Cells(r, c).Text
        c = c + 1
        If c > 18 Then c = 2: r = r + 1
     End Select
        
    Next txtbox
End Sub


Sub find_date_area()
  If ComboBox1.ListIndex = -1 Then Exit Sub
  If ComboBox2.ListIndex = -1 Then Exit Sub
  wRow = ComboBox2.ListIndex + 2
  wCol = ComboBox1.ListIndex + 2
  TextBox53 = wsData.Cells(wRow, wCol).Text
End Sub


Private Sub ComboBox1_Change()
  Call find_date_area
End Sub


Private Sub ComboBox2_Change()
  Call find_date_area
End Sub


Private Sub CommandButton3_Click()
    Dim aOutlook As Object
    Dim aEmail As Object
    Dim rngeAddresses As Range, rngeCell As Range, strRecipients As String
    Set aOutlook = CreateObject("Outlook.Application")
    Set aEmail = aOutlook.CreateItem(0)
        With aEmail
            .htmlBody = "Hi There," & _
            "MPAN / MPRN: " & _
            "Post Code: " & _
            "Comments:  " & _
            "Job Type: " & _
            "Many thanks "
            .To = Worksheets("Email Links").Range("A2").Value
            .CC = ""
            .BCC = ""
            .Subject = "AMR - 2 Man Request"
            .Display
        End With
End Sub


Private Sub UserForm_Initialize()
    Dim DateStr As String, ResultStr As String
    Dim c As Long
    Dim i As Integer, txtbox As Integer
    Dim Count As Integer
    
    Set wsData = ThisWorkbook.Worksheets("Data")
    
    c = 2
    
    For txtbox = 1 To 18
        If txtbox <> 12 Then
            DateStr = wsData.Cells(1, c).Text
            ResultStr = Right(DateStr, 1)
        For i = Len(DateStr) - 1 To 1 Step -1
            ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
        Next i
        With Me.Controls("TextBox" & txtbox)
            .MultiLine = vbTrue
            .Text = ResultStr
        End With
            c = c + 1
        End If
        ResultStr = ""
    Next txtbox
    
'build class for textbox change event
    For txtbox = 34 To 122
        Select Case txtbox
            Case 51, 52, 54
                    
            Case Else
                Count = Count + 1
                ReDim Preserve TextBox(1 To Count)
                Set TextBox(Count).TextBoxClass = Me.Controls("TextBox" & txtbox)
        End Select
    Next txtbox
                         
    RefreshTable
                    
    ComboBox1.RowSource = ""
    ComboBox2.RowSource = ""
                    
    With wsData
        ComboBox1.List = Application.Transpose(.Range("B1:R1").Value)
        ComboBox2.List = .Range("A2:R" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    End With
                    
End Sub
ENSURE that you copy ALL the code including the variables that MUST sit at the very TOP of your forms code page OUTSIDE any procedure.

Hopefully, if you followed my instructions, you should now find that the class event you added will now do the same job as those textbox change events you had.

Dave
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
925
Wow that is amazing thank you, that makes the code to be so much shorter, thankyou for everything
 

Forum statistics

Threads
1,077,687
Messages
5,335,662
Members
399,032
Latest member
thefinu

Some videos you may like

This Week's Hot Topics

Top