Page 3 of 5 FirstFirst 12345 LastLast
Results 21 to 30 of 46

Thread: Update cell after update from userform

  1. #21
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,054
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Update cell after update from userform

    Quote Originally Posted by Patriot2879 View Post
    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 by dmt32; Sep 19th, 2019 at 04:14 AM.

  2. #22
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    916
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update cell after update from userform

    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?

  3. #23
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,054
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Update cell after update from userform

    Quote Originally Posted by Patriot2879 View Post
    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

  4. #24
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    916
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update cell after update from userform

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

  5. #25
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,054
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Update cell after update from userform

    Quote Originally Posted by Patriot2879 View Post
    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

  6. #26
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    916
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update cell after update from userform

    Hi Dave, yes I have done that

  7. #27
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,054
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Update cell after update from userform

    Quote Originally Posted by Patriot2879 View Post
    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

  8. #28
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    916
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update cell after update from userform

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

  9. #29
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,054
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Update cell after update from userform

    Quote Originally Posted by Patriot2879 View Post
    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

  10. #30
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    916
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update cell after update from userform

    just outstanding thank you

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •