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

0
Utf
12/8/2009 2:36:01 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
857 Views

Similar Articles

[PageSpeed] 48

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
> 
0
Utf
12/8/2009 3:27:01 PM
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
0
Dave
12/8/2009 5:40:03 PM
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
> .
> 
0
Utf
12/10/2009 3:24:11 AM
Reply:

Similar Artilces:

I need to keep a count of the times a cell is changed
I have a cell that contains a date of which something was last used. Is there anyway that everytime I change that date, I have another cell that keeps a count of that? For instance if A1 contains 8/28/2005. . .this is the first instance so therefore B1 contains 1. If I change A1 to 9/28/2005 then B1 would automatically add 1 to its count so it now shows 2. Any help would be greatly appreciated. Hi Paul, Yo can do this with a Change event procedure (change cell references "A1" and "B1" as you need them): Private Sub Worksheet_Change(ByVal Target As Range) If Tar...

List box
first of all thank u in advance.... Hope this is possible I want to use a list box to search a record to show on the reports.. after selecting a record in the list box they can preview the report but I have 2 types of reports that is depend on the selected data.. please see below CompanyName State Layer Company1 NY 1 Company1 DC 1 Company1 PA 2 Company2 NY 1 Company2 DC 1 If user select a company has location in NY and Rank is ...

std::list, std::vector what would be best?
Hi, I have an application that logs certain events, (not OS events but my own application events). To log the events themselves I created a structure. struct MY_EVENTS{ int m_iEvent; void*m_pBuffer; // buffer containing data relevant to the event itself. int m_iBufferLen; // the len of the buffer /* copy constructor and assignment constructors as well. */ }; I then created a std::list purely because it allowed me to insert data in front as well as at the back of the template(?). But since then, after a few changes in the design, I realized that I no ...

select two option to one criteria
thanks for you help yesterday with a dsum just one quick question this code below ..... [cbMonth] & "' And [Revenue Stream Division] = 'Production'") how do i get it to select "Others" as well in [Revenue Stream Division] so something like [Revenue Stream Division] = 'Production' & 'other'") but that didn't work thanks ..... [cbMonth] & "' And ([Revenue Stream Division] = 'Production' Or [Revenue Stream Division] = 'Other')") "Alan" wrote: > >...

Message Boxes
When displaying a message box how do you insert rows, carriage returns and place bullit points on left hand side of box. I've seen how to do it somewhere and I know it's dead simple but can't remember where!!! Thanx all!!! Mikey, Use the concatenate operator, &, with the Chr(10) (line feed) code: MsgBox "First line" & chr(10) & "Second line" To keep the code cleaner-looking, you may want to use the form: MsgBox "This is the first line" & Chr(10) & _ "This is the second" & Chr(10) & _ "And the fina...

Birthday List
I am running HQ w/ 2 SO's 1.3. I own a pet related biz, and (in additional information) I collect customers' dogs' names and birthdays (up to 4 dogs per customer). Each month I would like to mail customers birthday cards/promos based on their pet's birthday. My problem is that my report criteria is quite lengthy. I have to set each "Birthday" field with a range "06/01/1996 to 06/30/1996" "06/01/1997 to 06/30/1997" etc. Then I have to do the same multiple entries for 3 more "Birthday" fields. Is there a way to just use a Fi...

Can Drop Down Boxes jump to the answer as you type?
If I create a form with VBA, to create a drop down box to have a list to pick an answer for to fill in a cell in a worksheet, it will jump to the answer in the list as you type. Can a Data|Validation|List do the same thing? If not, is there a way to get that functionality in Excel? Data Validation doesn't support autocomplete. If you can use programming, there are instructions here for adding a combobox from which you can select one of the values from the data validation list. In the combobox, you can enable autocomplete: http://www.contextures.com/xlDataVal11.html rrucksda...

Open Userforms from a Dropdown list
Hello, and thanks for the help, I have several userforms and would like to place the names of the forms into a dropdown list to select the form i wish to use is this possible. again thanks ...

Option to hide cost in eTech
There needs to be an option to hide the inventory cost information in eTech. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=7f00f47c-8035-469d-b35f-e97459e9e913&dg=micr...

saving values to cells
ok here goes... what i was wondering is hard to explain so ill explain it in an smal example... i have 3 sheets in my workbook, 'week1', 'week2' and 'total' i enter the number 10 in cell A1 on the 'week1' sheet and the number in cell B2 on the 'week2' sheet on the 'total' sheet in cell C3 i enter the formula =week1!A1+week2!B and it adds it obviously. but i then want to delete sheets 'week1' and 'week2' yet keep the valu 13 in cell C3 on the 'total' sheet WITHOUT having to type it in myself. ignore the macro part i...

Setting focus to List Control
Hi all, I have a List Control that I fill in OnSetActive. I then want to highlight the first row. I tried to do it as follows: BOOL PassTab::OnSetActive() { // ... fill the List Control c_selectedList.SetItemState( 0, LVIS_SELECTED, LVIS_SELECTED); c_selectedList.SetFocus(); return TRUE; } The first item gets a grey rectangle around it, not the blue one that it has when it's selected *and* focused, so from this I deduce that the command c_selectedList.SetFocus(); is not working here. When I use the same two lines elsewhere (say, when I press a button) the first ...

Creating a userform?
I would like to enter data to a speadsheet with a userform. I want enter the data in a form that will automatically insert the data to the correct cells in my spreadsheet. And then allow me to enter new data on the next line below. Is this possible? I have just put up a file for you at:- http://www.pierrefondes.com/ It is item number 42 towards the top of my home page. In here I have tried to explain, as clearly as possible, what you need to do to set up and use a Form in EXCEL 2007. There is a simple worked example for you to work through if you want to. Please d...

Problem saving embedded .gif or .jpg (.bmp only option)
(Sorry for crosspost (& repost - first had error)- also posted in "microsoft.public.outlook.general") I've seen this question posted a half dozen times, and I've yet to see a solution. Problem: When an animated .gif (ditto for .jpg) is embedded in the body of a message (Outlook 2002 (Windows XP (home) with Office XP 2002) - I'm NOT using Outlook Express), the ONLY option I have for saving the embedded .gif (or .jpg) is in .bmp format - no other option is available. (See attached screen print for clarification.) I have deleted all Temp Internet Folders & files;...

Using Exception List in Anit-Spam Rules
I apologize for the broad audience, but I can't find a more specific group appropriate to this topic. I'm using Outlook XP (2002). The Rules Wizard shows a built-in rule called Exception List that says "Apply this rule after message arrives from senders on my Exception List. Do not search message for commercial or adult content" (Exception List is underlined like a personal contact or distribution list). It also provides a dialog for maintaining the exception list. Question is, how can I make use of this list in my own rules. I would like to create rules based on conten...

group address list
Hello, I can see all the address in GAL, but how can I group them in a way as department? e.g. all account staff in a group called "Accounting" and we can select one or some of them from the group? Rather than a distribution group? Thanks. On Thu, 30 Mar 2006 23:47:01 -0800, Chris <Chris@discussions.microsoft.com> wrote: >Hello, I can see all the address in GAL, but how can I group them in a way as >department? e.g. all account staff in a group called "Accounting" and we can >select one or some of them from the group? Rather than a distribution group? ...

How do you keep favorite folders list in outlook
I have set up a folders list in outlook 2003 under mail favorite folders. Each time I re open outlook the folder list has gone and i have to start again. How do you keep list once you have created it. Alex G ...

Save a file in pub, now wont open ???
I recently created and saved a file as .pub. I have been able to open it up several times, but now the Publisher program says it cant open the file ! It will open on one computer but not the other...both have 2007 pulisher programs... and when I did have it opened, I saved it to a cd...that wont open it either. I am wondering if something in the properties has to do with this problem. I have never opened that up to save anything differently...any ideas??? How to troubleshoot a damaged publication in Publisher http://support.microsoft.com/default.aspx?scid=kb;en-us;198256 Have...

How do i change what opens when i start Microsoft word?
when i open word one of my final exam papers from last semester automatically opens. I've tried changing my "normal" file, deleting macros, nothing works. How do i set the default document to open up to be blank? Take a look here: http://word.mvps.org/fAQs/AppErrors/BlankDocNotBlank.htm If editing Normal.dot/Normal.dotm doesn't fix the problem, make sure you're editing the correct one. Some people have multiple copies in different folders. -- Herb Tyson MS MVP Author of the Word 2007 Bible Blog: http://word2007bible.herbtyson.com Web: http://www.he...

opening a userform from a userform
Is there a way to open and use a userform from another userform? I am using userforms for data entry and would like the option to open a second form whilst entering data into a form Can anyone help? [excel 2003] Just Show it. Call is like this... UserForm2.Show or like this... UserForm2.Show vbModeless depending on whether you want UserForm2 to be modal or non-modal. Remember to change my example UserForm name from UserForm2 to whatever the name your second UserForm is. -- Rick (MVP - Excel) "Roger on Excel" <RogeronExcel@discussions.microsof...

Distribution List Limits
Hi, I could find an answer to this for Outlook 2002 but not for Outlook 2003. Is the answer the same? We are using SBS 2003. The error is: "The distribution list has reached the maximum size for your network e-mail server. The new member could not be added." List was at 132 members. Not an Outlook question. These limits are set by the server, not Outlook. If your sever hasn't changed, then I imagine the limit is still the same. -- Russ Valentine [MVP-Outlook] "Paddy Ryan" <paddy@fpmt.org> wrote in message news:uOKDg0vaGHA.4196@TK2MSFTNGP03.phx.gbl... ...

Text box with iif and is null statements
I need to have a text box that combines a couple of fields and text. My problem is that if the field is blank I need it not to include some of the text. The text box is Dear Alias or FirstName and SpouseName, What I have is ="Dear " & (IIf(IsNull([Alias]),StrConv([FirstName],3),StrConv([Alias],3))) & (IIf(IsNull([SpouseName]),StrConv([SpouseName],3))) & "," Problem is that if there is a spousename it isn't showing. If I add in the "and" I still don't get the spouse name but I get the and. I have tried so many combinatio...

Distribution Lists Viewing Bar and Other Wants in Outlook 2007 with Vista
I recently upgraded to Office 2007 from 2000 Professional . . . and miss being able to view the distribution lists in the viewing bar to the left of the contacts that was available in Outlook 2000. And it would be nice to be able to "select all", see the total number of contacts, drag and drop, and copy along with seeing what distribution lists a contact belongs to like in Outlook 2000. Or is one or more of these Outlook 2000 distribution list features planned or available as add-ins for Outlook 2007? D. Bancroft ...

point tasks list in outlook today to a public folder task list
I want the Outlook Today page to show tasks located in a public folder not the personal folder task list. Is there a way to do this. It isn't an option in the outlook today customization options. ...

passwords - userforms
[Excel 2003] I have a spreadsheet which uses forms to enter and display data. The Spreadsheet uses a main page form "frmDashboard" from which other userforms are activated using command buttons. The form frmDashoboard remains open in the background as a desktop while the user activates the other forms. I am looking at various methods to password protect the opening of other forms from the main useform frmDashboard and I use the following code to do this: Private Sub CommandButton1_Click() Dim i_pwd As String i_pwd = InputBox("Please Enter Password&q...

Text Box Problem
On a form, I am attempting to add numbers (currency) in a text box based on fields in a record. The sum displays correctly when there are values (or 0) in all of the 5 fields being added but displays nothing when one or more of the fields is NULL. Is there a way to force the sum to appear when one (or more) of the added fields is NULL? Thanks -- Dennis Nz(Field1, 0) + Nz(Field2, 0) + Nz(Field3, 0) + Nz(Field4, 0) + Nz(Field5, 0) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "denyoung" <denyoung@discussions...