Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform List boxes - editing - then option to save changes (or no
[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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform List boxes - editing - then option to save changes (or no
Please note I meant text boxes in the title
Apologies, Roger "Roger on Excel" wrote: [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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform List boxes - editing - then option to save changes (or no
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 Roger on Excel wrote: [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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform List boxes - editing - then option to save changes (o
Thanks Again Dave,
Great code - so much to learn here and this is incredibly helpful All the best, Roger "Dave Peterson" wrote: 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 Roger on Excel wrote: [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 -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userform on intranet: disble save option, allow open only | Excel Programming | |||
Missing list and option boxes | Excel Discussion (Misc queries) | |||
Editing color/msoGradiant of shapes, option button and text boxes | Excel Programming | |||
A general question about editing current list via UserForm | Excel Programming | |||
Option Buttons and List Boxes | Excel Programming |