Page 1 of 5 123 ... LastLast
Results 1 to 10 of 46

Thread: Update cell after update from userform

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

    Default Update cell after update from userform

    HI i hope you can help me with the code below as this is not working an i am trying to update a cell in a sheet called 'Data' if changes are made in my userform, my range in the 'Data' sheet my range is A1:R17 , ROWS B1:R1 have dates inand columns A2:A17 have areas in, then B2:R17 have numbers in.

    In
    ComboBox1 and ComboBox2 this is a lookupfor the date and area where it cross references and find the number in thecorresponding cell and put this number into TextBox53. In TextBox54 this iswhere the number can be updated. What i want is then this to update the correspondingcell from A1:R17.




    - ComboBox1 contains adate matching one of the dates in B1:R1 and

    - ComboBox2 contains an area matching one of the areas in A2:A17 and
    - TextBox54 is entered manually


    Hope you can help with the code please?


    Code:
    Option Explicit
    
    
    
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
    
    
    
      Dim a() As Variant,i As Long, j As Long
    
    
      Dim sThisFullName AsString, sSynchronized As String
    
    
      Dim Wb As Workbook,IsOpen As Boolean
    
    
      Dim FullName AsVariant, FullNames As Range
    
    
    
    
    
      If Target.Address<> "TextBox54" Or Target.Value = "" Then Exit Sub
    
    
    
    
    
      ' Determine Row #and  Column #
    
    
      i = Application.Match(Range("ComboBox2"),Range("A1:A17"), 0)
    
    
      If i = 0 Then MsgBoxRange("ComboBox1") & " not found in A1:A18",vbCritical: Exit Sub
    
    
      j =Application.Match(Range("ComboBox1"), Range("A1:R18"), 0)
    
    
      If i = 0 Then
    
    
        MsgBoxRange("ComboBox2").Value & " not found in A1:A17",vbCritical
    
    
        Exit Sub
    
    
      ElseIf j = 0 Then
    
    
        MsgBoxRange("ComboBox1").Value & " not found in A1:R1",vbCritical
    
    
        Exit Sub
    
    
      End If
    
    
    
    
    
      ' Disable eventshandling, enable auto calculation
    
    
     Application.EnableEvents = False
    
    
     Application.Calculation = xlCalculationAutomatic
    
    
    
    
    
      ' Adjust theIntersection cell Value by substracting Input in TextBox54
    
    
      Cells(i, j).Value =Cells(i, j).Value - Target.Value
    
    
    
    
    
      ' Clear ONLY Targetcell and select it
    
    
      Target.ClearContents
    
    
      Target.Select
    
    
    
    
    
      ' Disable blinking
    
    
     Application.ScreenUpdating = False
    
    
    
    
    
      i =UBound(FullNames.Value) - 1
    
    
      j = 0
    
    
      sThisFullName =LCase(ThisWorkbook.FullName)
    
    
      a() =Me("Data").Range("A1").CurrentRegion.Value
    
    
      For Each FullName InFullNames.Value
    
    
        If InStr(FullName,"") > 0 And LCase(FullName) <> sThisFullName Then
    
    
          j = j + 1
    
    
         Application.StatusBar = "Updating (" & j &"/" & i & "): " & FullName
    
    
          On Error ResumeNext
    
    
          Set Wb =Workbooks(Mid(FullName, InStrRev(FullName, "") + 1))
    
    
          IsOpen = (Err =0)
    
    
          On Error GoToexit_
    
    
          If Not IsOpenThen
    
    
            Set Wb =Workbooks.Open(FullName, UpdateLinks:=False)
    
    
          End If
    
    
          With Wb
    
    
           .Sheets("Data")(Me.Name).Range("A1").CurrentRegion.Resize(UBound(a),UBound(a, 2)).Value = a()
    
    
            .Save
    
    
            If Not IsOpenThen .Close False
    
    
          End With
    
    
          sSynchronized =sSynchronized & IIf(j > 1, vbLf, "") & FullName
    
    
        End If
    
    
      Next
    
    
     ThisWorkbook.Activate
    
    
    
    
    
    exit_:
    
    
    
    
    
      ' Restore eventshandling, screen updating and status bar
    
    
     Application.EnableEvents = True
    
    
     Application.ScreenUpdating = True
    
    
     Application.StatusBar = False
    
    
    
    
    
      ' Inform about error
    
    
      If Err Then
    
    
        MsgBoxErr.Description, vbCritical, "Error!"
    
    
      Else
    
    
        ' Put updatinginfo in the comment of TextBox54
    
    
        If Target.CommentIs Nothing Then Target.AddComment
    
    
        WithTarget.Comment
    
    
          .Visible = True
    
    
          .TextText:="[Updated " & j & " workbook(s) on " &Now & "]" & vbLf & sSynchronized
    
    
          .Shape.TextFrame.AutoSize= True
    
    
         .Shape.TextFrame.AutoSize = False
    
    
        End With
    
    
      End If
    
    
    
    
    
    End Sub









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

    Default Re: Update cell after update from userform

    Hi I have added the file to the dropbox hopefully you can have a look please.
    https://www.dropbox.com/s/roi10ux8jsstja8/Capacity2.xlsm?dl=0

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

    Default Re: Update cell after update from userform

    When the coding is in the majority of the lines are in red if that helps

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

    Default Re: Update cell after update from userform

    Hi I hope you can help? hope you can open my dropbox file

  5. #5
    Board Regular
    Join Date
    Mar 2013
    Posts
    793
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update cell after update from userform

    Writing textbox54 to the sheet is the same as loading textbox53 except
    for 53 the textbox equals the sheet cell and for 54 the sheet cell equals the textbox.

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

    Default Re: Update cell after update from userform

    Sorry I am not following what I need to do. I need it to look in the data sheet to update

  7. #7
    Board Regular
    Join Date
    Mar 2013
    Posts
    793
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update cell after update from userform

    Look at the code in the user form that you are using to load the cell value into textbox53.

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

    Default Re: Update cell after update from userform

    Hi ok o try but how do I deduct

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

    Default Re: Update cell after update from userform

    Hi I have tried the below but I get an error on the textbox54 line
    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)
      TextBox54 = Sheets("TextBox54").Cells(wRow, wCol)
    End Sub

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

    Default Re: Update cell after update from userform

    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.

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
  •