Userform List boxes - editing - then option to save changes (or no

[Roger on Excel]

[Excel 2003]

I populate text boxes in userforms using the following type of code to
retrieve data from the worksheets:

Private Sub GetData_Click()

Dim ws As Worksheet
Set ws = Worksheets("Sheet 1")
Me.txt1.Value = ws.Range("A1").Value
Me.txt2.Value = ws.Range("B2").Value
Me.txt3.Value = ws.Range("C3").Text
End Sub

I can edit the text boxes and then send the edited information back to the
worksheets using:

Private Sub PutData_Click()
Dim ws As Worksheet
Set ws = Worksheets("Sheet 1")
ws.Range("A1").Value = Me.txt1.Value
ws.Range("B2").Value = Me.txt2.Value
ws.Range("C3").Value = Me.txt3.Value
End Sub

What I need is for additional code to check whether the retrieved data has
been changed by the user (in the form) and warn the user to update the
changes before they exit the form. A user may decide not to accept the
changes so I would need the original GetData information to be stored so that
it could be re-written over any unwanted changes.

Can anyone help with this?

Thank You, Roger

 

[Roger on Excel]

Please note I meant text boxes in the title
Apologies,

Roger

 

[Dave Peterson]

You have at least a couple of choices.

You could just look at the cells and compare them to the values in the
textboxes. If the data is the same, no warning. If the data is different, then
warn the user.

Another option is to keep track if your input is "dirty". Kind of like excel
does with its .saved property. If a user makes any change--even a change to the
same value, then excel changes the .saved property to false.

You could do that kind of thing this way:

Option Explicit
Dim IsSaved As Boolean
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim NextCol As Long

With Worksheets("Sheet1")
NextCol = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
For iCtr = 1 To 3
.Cells(iCtr, NextCol).Value _
= Me.Controls("textbox" & iCtr).Value
Next iCtr
End With
'load the next ????
Me.CommandButton1.Enabled = False
IsSaved = True
End Sub
Private Sub CommandButton2_Click()
Dim resp As Long

If IsSaved = True Then
'let them leave
Else
resp = MsgBox(Prompt:="Wanna save last changes?", _
Buttons:=vbYesNo)
If resp = vbYes Then
Call CommandButton1_Click 'the save button
End If
End If
Unload Me
End Sub
Private Sub TextBox1_Change()
Call ChkSaveBtn
End Sub
Private Sub TextBox2_Change()
Call ChkSaveBtn
End Sub
Private Sub TextBox3_Change()
Call ChkSaveBtn
End Sub
Private Sub ChkSaveBtn()
IsSaved = False
Me.CommandButton1.Enabled = True
End Sub
Private Sub UserForm_Initialize()

Dim iCtr As Long
With Worksheets("Sheet1")
For iCtr = 1 To 3
Me.Controls("Textbox" & iCtr).Value _
= .Cells(iCtr, "A").Value
Next iCtr
End With

With Me.CommandButton1
.Caption = "Save"
.Default = True
.Enabled = False
End With

With Me.CommandButton2
.Caption = "Cancel"
.Cancel = True
.Enabled = True
End With

IsSaved = True

End Sub

[Excel 2003]

I populate text boxes in userforms using the following type of code to
retrieve data from the worksheets:

Private Sub GetData_Click()

Dim ws As Worksheet
Set ws = Worksheets("Sheet 1")
Me.txt1.Value = ws.Range("A1").Value
Me.txt2.Value = ws.Range("B2").Value
Me.txt3.Value = ws.Range("C3").Text
End Sub

I can edit the text boxes and then send the edited information back to the
worksheets using:

Private Sub PutData_Click()
Dim ws As Worksheet
Set ws = Worksheets("Sheet 1")
ws.Range("A1").Value = Me.txt1.Value
ws.Range("B2").Value = Me.txt2.Value
ws.Range("C3").Value = Me.txt3.Value
End Sub

What I need is for additional code to check whether the retrieved data has
been changed by the user (in the form) and warn the user to update the
changes before they exit the form. A user may decide not to accept the
changes so I would need the original GetData information to be stored so that
it could be re-written over any unwanted changes.

Can anyone help with this?

Thank You, Roger

 

[Roger on Excel]

Thanks Again Dave,

Great code - so much to learn here and this is incredibly helpful

All the best,

Roger

You have at least a couple of choices.

You could just look at the cells and compare them to the values in the
textboxes. If the data is the same, no warning. If the data is different, then
warn the user.

Another option is to keep track if your input is "dirty". Kind of like excel
does with its .saved property. If a user makes any change--even a change to the
same value, then excel changes the .saved property to false.

You could do that kind of thing this way:

Option Explicit
Dim IsSaved As Boolean
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim NextCol As Long

With Worksheets("Sheet1")
NextCol = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
For iCtr = 1 To 3
.Cells(iCtr, NextCol).Value _
= Me.Controls("textbox" & iCtr).Value
Next iCtr
End With
'load the next ????
Me.CommandButton1.Enabled = False
IsSaved = True
End Sub
Private Sub CommandButton2_Click()
Dim resp As Long

If IsSaved = True Then
'let them leave
Else
resp = MsgBox(Prompt:="Wanna save last changes?", _
Buttons:=vbYesNo)
If resp = vbYes Then
Call CommandButton1_Click 'the save button
End If
End If
Unload Me
End Sub
Private Sub TextBox1_Change()
Call ChkSaveBtn
End Sub
Private Sub TextBox2_Change()
Call ChkSaveBtn
End Sub
Private Sub TextBox3_Change()
Call ChkSaveBtn
End Sub
Private Sub ChkSaveBtn()
IsSaved = False
Me.CommandButton1.Enabled = True
End Sub
Private Sub UserForm_Initialize()

Dim iCtr As Long
With Worksheets("Sheet1")
For iCtr = 1 To 3
Me.Controls("Textbox" & iCtr).Value _
= .Cells(iCtr, "A").Value
Next iCtr
End With

With Me.CommandButton1
.Caption = "Save"
.Default = True
.Enabled = False
End With

With Me.CommandButton2
.Caption = "Cancel"
.Cancel = True
.Enabled = True
End With

IsSaved = True

End Sub

[Excel 2003]

I populate text boxes in userforms using the following type of code to
retrieve data from the worksheets:

Private Sub GetData_Click()

Dim ws As Worksheet
Set ws = Worksheets("Sheet 1")
Me.txt1.Value = ws.Range("A1").Value
Me.txt2.Value = ws.Range("B2").Value
Me.txt3.Value = ws.Range("C3").Text
End Sub

I can edit the text boxes and then send the edited information back to the
worksheets using:

Private Sub PutData_Click()
Dim ws As Worksheet
Set ws = Worksheets("Sheet 1")
ws.Range("A1").Value = Me.txt1.Value
ws.Range("B2").Value = Me.txt2.Value
ws.Range("C3").Value = Me.txt3.Value
End Sub

What I need is for additional code to check whether the retrieved data has
been changed by the user (in the form) and warn the user to update the
changes before they exit the form. A user may decide not to accept the
changes so I would need the original GetData information to be stored so that
it could be re-written over any unwanted changes.

Can anyone help with this?

Thank You, Roger