Update cell after update from userform

[dmt32]

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

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

 

[Patriot2879]

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]

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

 

[Patriot2879]

Hi Dave I got it too work deleted the coding as described now all working

 

[dmt32]

Hi Dave I got it too work deleted the coding as described now all working

Have you inserted the Class Module & added the code in way I described in #post 21?

Dave

 

[Patriot2879]

Hi Dave, yes I have done that

 

[dmt32]

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

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]

Wow that is amazing thank you, that makes the code to be so much shorter, thankyou for everything

 

[dmt32]

Wow that is amazing thank you, that makes the code to be so much shorter, thankyou for everything

Most welcome - glad it all helped


Dave

 

[Patriot2879]

just outstanding thank you