Update cell after update from userform

[Patriot2879]

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?

[FONT=Calibri]Option Explicit[/FONT]


[FONT=Calibri] [/FONT]


[FONT=Calibri]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT]


[FONT=Calibri] [/FONT]


[FONT=Calibri]  Dim a() As Variant,i As Long, j As Long[/FONT]


[FONT=Calibri]  Dim sThisFullName AsString, sSynchronized As String[/FONT]


[FONT=Calibri]  Dim Wb As Workbook,IsOpen As Boolean[/FONT]


[FONT=Calibri]  Dim FullName AsVariant, FullNames As Range[/FONT]


[FONT=Calibri] [/FONT]


[FONT=Calibri]  If Target.Address<> "TextBox54" Or Target.Value = "" Then Exit Sub[/FONT]


[FONT=Calibri] [/FONT]


[FONT=Calibri]  ' Determine Row [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=and]#and[/URL]  Column #[/FONT]


[FONT=Calibri]  i = Application.Match(Range("ComboBox2"),Range("A1:A17"), 0)[/FONT]


[FONT=Calibri]  If i = 0 Then MsgBoxRange("ComboBox1") & " not found in A1:A18",vbCritical: Exit Sub[/FONT]


[FONT=Calibri]  j =Application.Match(Range("ComboBox1"), Range("A1:R18"), 0)[/FONT]


[FONT=Calibri]  If i = 0 Then[/FONT]


[FONT=Calibri]    MsgBoxRange("ComboBox2").Value & " not found in A1:A17",vbCritical[/FONT]


[FONT=Calibri]    Exit Sub[/FONT]


[FONT=Calibri]  ElseIf j = 0 Then[/FONT]


[FONT=Calibri]    MsgBoxRange("ComboBox1").Value & " not found in A1:R1",vbCritical[/FONT]


[FONT=Calibri]    Exit Sub[/FONT]


[FONT=Calibri]  End If[/FONT]


[FONT=Calibri] [/FONT]


[FONT=Calibri]  ' Disable eventshandling, enable auto calculation[/FONT]


[FONT=Calibri] Application.EnableEvents = False[/FONT]


[FONT=Calibri] Application.Calculation = xlCalculationAutomatic[/FONT]


[FONT=Calibri] [/FONT]


[FONT=Calibri]  ' Adjust theIntersection cell Value by substracting Input in TextBox54[/FONT]


[FONT=Calibri]  Cells(i, j).Value =Cells(i, j).Value - Target.Value[/FONT]


[FONT=Calibri] [/FONT]


[FONT=Calibri]  ' Clear ONLY Targetcell and select it[/FONT]


[FONT=Calibri]  Target.ClearContents[/FONT]


[FONT=Calibri]  Target.Select[/FONT]


[FONT=Calibri] [/FONT]


[FONT=Calibri]  ' Disable blinking[/FONT]


[FONT=Calibri] Application.ScreenUpdating = False[/FONT]


[FONT=Calibri] [/FONT]


[FONT=Calibri]  i =UBound(FullNames.Value) - 1[/FONT]


[FONT=Calibri]  j = 0[/FONT]


[FONT=Calibri]  sThisFullName =LCase(ThisWorkbook.FullName)[/FONT]


[FONT=Calibri]  a() =Me("Data").Range("A1").CurrentRegion.Value[/FONT]


[FONT=Calibri]  For Each FullName InFullNames.Value[/FONT]


[FONT=Calibri]    If InStr(FullName,"") > 0 And LCase(FullName) <> sThisFullName Then[/FONT]


[FONT=Calibri]      j = j + 1[/FONT]


[FONT=Calibri]     Application.StatusBar = "Updating (" & j &"/" & i & "): " & FullName[/FONT]


[FONT=Calibri]      On Error ResumeNext[/FONT]


[FONT=Calibri]      Set Wb =Workbooks(Mid(FullName, InStrRev(FullName, "") + 1))[/FONT]


[FONT=Calibri]      IsOpen = (Err =0)[/FONT]


[FONT=Calibri]      On Error GoToexit_[/FONT]


[FONT=Calibri]      If Not IsOpenThen[/FONT]


[FONT=Calibri]        Set Wb =Workbooks.Open(FullName, UpdateLinks:=False)[/FONT]


[FONT=Calibri]      End If[/FONT]


[FONT=Calibri]      With Wb[/FONT]


[FONT=Calibri]       .Sheets("Data")(Me.Name).Range("A1").CurrentRegion.Resize(UBound(a),UBound(a, 2)).Value = a()[/FONT]


[FONT=Calibri]        .Save[/FONT]


[FONT=Calibri]        If Not IsOpenThen .Close False[/FONT]


[FONT=Calibri]      End With[/FONT]


[FONT=Calibri]      sSynchronized =sSynchronized & IIf(j > 1, vbLf, "") & FullName[/FONT]


[FONT=Calibri]    End If[/FONT]


[FONT=Calibri]  Next[/FONT]


[FONT=Calibri] ThisWorkbook.Activate[/FONT]


[FONT=Calibri]  [/FONT]


[FONT=Calibri]exit_:[/FONT]


[FONT=Calibri] [/FONT]


[FONT=Calibri]  ' Restore eventshandling, screen updating and status bar[/FONT]


[FONT=Calibri] Application.EnableEvents = True[/FONT]


[FONT=Calibri] Application.ScreenUpdating = True[/FONT]


[FONT=Calibri] Application.StatusBar = False[/FONT]


[FONT=Calibri] [/FONT]


[FONT=Calibri]  ' Inform about error[/FONT]


[FONT=Calibri]  If Err Then[/FONT]


[FONT=Calibri]    MsgBoxErr.Description, vbCritical, "Error!"[/FONT]


[FONT=Calibri]  Else[/FONT]


[FONT=Calibri]    ' Put updatinginfo in the comment of TextBox54[/FONT]


[FONT=Calibri]    If Target.CommentIs Nothing Then Target.AddComment[/FONT]


[FONT=Calibri]    WithTarget.Comment[/FONT]


[FONT=Calibri]      .Visible = True[/FONT]


[FONT=Calibri]      .TextText:="[Updated " & j & " workbook(s) on " &Now & "]" & vbLf & sSynchronized[/FONT]


[FONT=Calibri]      .Shape.TextFrame.AutoSize= True[/FONT]


[FONT=Calibri]     .Shape.TextFrame.AutoSize = False[/FONT]


[FONT=Calibri]    End With[/FONT]


[FONT=Calibri]  End If[/FONT]


[FONT=Calibri] [/FONT]


[FONT=Calibri]End Sub[/FONT]

 

[Patriot2879]

hi Dave would it be easy to amend the link to the 'Data' sheet? to C:\Users\s21375\Desktop\Book1.xlsm and sheet in book1 is called 'data'

 

[dmt32]

hi Dave would it be easy to amend the link to the 'Data' sheet? to C:\Users\s21375\Desktop\Book1.xlsm and sheet in book1 is called 'data'

Is this question related to your latest thread: https://www.mrexcel.com/forum/excel...en-then-msg-box-open-saying-userform-use.html

Short answer not easy especially if you are thinking of creating template copies of your workbook for multiple users where they read / write data to central workbook (database) over a corporate network but is doable.

This is a much better solution than sharing a workbook (which is best avoided) but does require some extensive coding to manage.


Dave

 

[Patriot2879]

Hi Dave yes related to my other thread, just another idea thought that this might be doable if the data is in another workbook and userforms are separate?

 

[Patriot2879]

Please could you advise how I could make template copies of m workbook for multiple users where they read / write data to central workbook (database) over a corporate network. this would be amazing.

 

[dmt32]

Hi Dave yes related to my other thread, just another idea thought that this might be doable if the data is in another workbook and userforms are separate?

You are not really meant to ask same question in two threads (I think)

With multi users, having template copies of a workbook connected to a database (Access ideal or another workbook will work) is the sensible approach but it does involve much more coding to manage. Also, data copied to master workbook from one user, other users with their templates open will not get real time updates but will have to refresh each time to see changes.

Worth noting that whilst users need read / write access, you cannot have the master workbook open in read / write mode.

Dave

 

[Patriot2879]

Hi Dave, what would you recommend? and how would I do this? sorry I thought my other thread was separate to this thread as different to the original subject.

 

[dmt32]

Hi Dave, what would you recommend? and how would I do this? sorry I thought my other thread was separate to this thread as different to the original subject.

Its a similar subject line which a MOD may (or may not) have a view on.

It it was a project I was doing I would connect template copies to a central database (Access ) but you can use another workbook as a simple database.
I have done many of these in past & it can be a lot of work.

If this is an approach you want to take you may do well to head to the Access forum & see if people there can assist. Or if want to use another workbook then create copy of your data tab & save it as separate file on shared network.

You then need to develop code for the template copies to

Open workbook read only to extract data to your table & close it

Open workbook read / write to write data to it save & close

Included in code you would need functions to manage errors & conflicts from other users amongst other things

And repeat what I said last time, you cannot have a master workbook open read / write whilst others need to write data to it also, they will not see real time updates.

Dave

 

[Patriot2879]

Hi Dave, ok thank you for the update what I would probably need is simple database in another workbook an make templates of the userform. but I don't have any idea on how to do that way over my had , also would it be possible o say when someone else is in another userform?

 

[dmt32]

Hi Dave, ok thank you for the update what I would probably need is simple database in another workbook an make templates of the userform. but I don't have any idea on how to do that way over my had , also would it be possible o say when someone else is in another userform?

Search the net, examples are out there

With template workbook you would not need to know if user(s) had their copies open if fact, they can leave them ope all day if want.
The one you cannot have open is the database workbook read / write mode. Only the code in template copies should do this.

Dave

 

[dmt32]

Please could you advise how I could make template copies of m workbook for multiple users where they read / write data to central workbook (database) over a corporate network. this would be amazing.

I found some spare time & decided to have a further look at your project & this request to share across network & think that as you a are using a fixed matrix for your data it mau be a little easier to manage with multi users than I first thought.

A possible solution would be to link the users template workbooks to a master (database) which when they update, changes should be reflected on other users copies. This process would all be managed by VBA code but idea would need further development & tested over your network.

Dave