Page 2 of 5 FirstFirst 1234 ... LastLast
Results 11 to 20 of 46

Thread: Update cell after update from userform

  1. #11
    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

    I hope I have made sense what I need textbox54 to do.

  2. #12
    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

    For example when somebody enters a number in textbox54 this gets deducted from textbox53 and gets updates in the 'data' sheet.

  3. #13
    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 have updated another link to my file on dropbox if ok, as I have done a few more updates, I hope you can help please.

    https://www.dropbox.com/s/7a9eywazcqud80j/Capacity3.xlsm?dl=0

  4. #14
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,036
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Update cell after update from userform

    Quote Originally Posted by Patriot2879 View Post
    how do I do the minus on this as well because once I enter a number in textbox54 this deducts from textbox53 and updates the correct cell in the 'Data' sheet, by cross referencing combobox1 and combobox2, hope you can help.
    Hi
    try following

    Make a BACKUP of your workbook & add following codes to your forms code page

    Code:
    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
                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
    Note:
    1 - Ensure that you DELETE any existing codes with the same name
    2 - Ensure that the variables shown in BOLD are placed at the very TOP of the forms code page OUTSIDE any procedure

    Replace your UserForm_Initialize code with the following

    Code:
    Private Sub UserForm_Initialize()
    Dim DateStr As String
    
    
    Set wsData = ThisWorkbook.Worksheets("Data")
    
    
    With wsData
    Dim ResultStr As String
    DateStr = .Range("B1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox1.MultiLine = vbTrue
    Me.TextBox1.Text = ResultStr
    
    
    DateStr = .Range("C1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox2.MultiLine = vbTrue
    Me.TextBox2.Text = ResultStr
    
    
    DateStr = .Range("D1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox3.MultiLine = vbTrue
    Me.TextBox3.Text = ResultStr
    
    
    DateStr = .Range("E1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox4.MultiLine = vbTrue
    Me.TextBox4.Text = ResultStr
    
    
    DateStr = .Range("F1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox5.MultiLine = vbTrue
    Me.TextBox5.Text = ResultStr
    
    
    DateStr = .Range("G1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox6.MultiLine = vbTrue
    Me.TextBox6.Text = ResultStr
    
    
    DateStr = .Range("H1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox7.MultiLine = vbTrue
    Me.TextBox7.Text = ResultStr
    
    
    DateStr = .Range("I1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox8.MultiLine = vbTrue
    Me.TextBox8.Text = ResultStr
    
    
    DateStr = .Range("J1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox9.MultiLine = vbTrue
    Me.TextBox9.Text = ResultStr
    
    
    DateStr = .Range("K1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox10.MultiLine = vbTrue
    Me.TextBox10.Text = ResultStr
    
    
    DateStr = .Range("L1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox11.MultiLine = vbTrue
    Me.TextBox11.Text = ResultStr
    
    
    DateStr = .Range("M1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox13.MultiLine = vbTrue
    Me.TextBox13.Text = ResultStr
    
    
    DateStr = .Range("N1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox14.MultiLine = vbTrue
    Me.TextBox14.Text = ResultStr
    
    
    DateStr = .Range("O1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox15.MultiLine = vbTrue
    Me.TextBox15.Text = ResultStr
    
    
    DateStr = .Range("P1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox16.MultiLine = vbTrue
    Me.TextBox16.Text = ResultStr
    
    
    DateStr = .Range("Q1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox17.MultiLine = vbTrue
    Me.TextBox17.Text = ResultStr
    
    
    DateStr = .Range("R1").Text
    ResultStr = Right(DateStr, 1)
    For i = Len(DateStr) - 1 To 1 Step -1
        ResultStr = ResultStr & vbCrLf & Mid(DateStr, i, 1)
    Next i
    Me.TextBox18.MultiLine = vbTrue
    Me.TextBox18.Text = ResultStr
    
    
    
    
    RefreshTable
    
    
    ComboBox1.RowSource = ""
    ComboBox1.List = Application.Transpose(.Range("B1:R1").Value)
    ComboBox2.RowSource = ""
    ComboBox2.List = .Range("A2:R" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    End With
    
    
    
    
    End Sub

    Hopefully will do what you want but adjust as required.

    Dave

  5. #15
    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 thank you I shall try this in the morning. Thank you very much.

  6. #16
    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

    Hello good morning, I have amended the code as advised thank you for this, but I get an error on the line below, hope you can help.

    [CODE]Sub find_date_area()
    [/CODE]

  7. #17
    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 please see below my test sheet, hopefully I did what you advised correctly, and if you can advise please where I have gone wrong.
    https://www.dropbox.com/s/kpnugulubcbgbj2/Capacitytest.xlsm?dl=0

  8. #18
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,036
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Update cell after update from userform

    Quote Originally Posted by Patriot2879 View Post
    Hi please see below my test sheet, hopefully I did what you advised correctly, and if you can advise please where I have gone wrong.
    https://www.dropbox.com/s/kpnugulubcbgbj2/Capacitytest.xlsm?dl=0
    In my post I had this line

    1 - Ensure that you DELETE any existing codes with the same name
    which you have not followed

    You have two codes that need to be deleted

    Code:
    Private Sub UserForm_Initialize()
    make sure you DELETE the OLD version the new one contains this line at the start

    Code:
    Set wsData = ThisWorkbook.Worksheets("Data")
    The UserForm_Initialize code that contains this is the one you need to keep

    The code below that looks like this needs to be DELETED

    Code:
    Sub find_date_area()
      If ComboBox1 = "" Or ComboBox1.ListIndex = -1 Then Exit Sub
      If ComboBox2 = "" Or ComboBox2.ListIndex = -1 Then Exit Sub
      Dim wRow As Long, wCol As Long
      wRow = ComboBox2.ListIndex + 2
      wCol = ComboBox1.ListIndex + 2
      TextBox53 = Sheets("Data").Cells(wRow, wCol)
    End Sub
    Let me know if all working ok & will show you how you can replace all those textbox change event codes with just one.

    Dave

  9. #19
    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 it working I didn't delete all what you advised, thisIs great thank you, please can you advise how when I put thenumber in I can clear textbox54? After its done its update

  10. #20
    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

    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

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
  •