PDA

View Full Version : Solved: UserForm & Spreadsheet updating problem



SailFL
09-01-2005, 02:42 AM
I have discovered what might become a serious problem for me with regards to using a UserForm to interface a Spreadsheet.

I created a UserForm to input and display data for a complicated spreadsheet. The spreadsheet is used to create a proposal for paver contractor. There are complicated formula. These formulas use cells that cacluate cost and materials needed for a job. The formulas use information from various cells to do the calculations. Changes to the top of the spreadsheet cell affects calculations towards the bottom. It works the same for changes at the bottom. They can affect cells at the top.

To make the form useful, I have a multipage form with 6 tabs. The user inputs quantity amounts or sq Ft and the amount of materials needed or price is calculated by the spreadsheet and then I display the value on the UserForm. The spreadsheet automatically updates all the cells when a value changes some place on the spreadsheet. But how do I update all my information when the cells change. For my UserForm to change the total price for a column of prices, I have to have an update for the total price for each of the sub items. For example: If there are 6 input line items, and the quantity triggers a cacluation for that line item, I get the total price from the spreadsheet and update the total price on the UserForm in each place that the quantity line item is UpDated.

I am new at programming VBA with Excel. How do make these kinds of updates? I hope I have been clear with what my problem is!

Thanks and If you have questions please ask them. I am not sure I can provide a code example. The spreadsheet is proprietary.

Thanks

Killian
09-01-2005, 04:19 AM
How do you populate the form in the first place? Presumably, on it's initialize event. If that's the case, if you put this code into it's own procedure, you can call it whenever you need to update the form i.e. on the worksheet_calculate event.
You may have other calculate events that you don't want to update the form on. Perhaps you could simplfy things by having a Update commmand button that triggers the worksheet calculation then updates the form?

SailFL
09-01-2005, 05:09 AM
There are items that I populate when the form is Initialized. But I use AfterUpdate to update when it is tied to an input field that the user inputs data.

I was hoping that I would not have to call to update everything. The procedure is an excellent idea but now I will have to add that call to many AfterUpdate....

Thanks

Killian
09-01-2005, 08:16 AM
Well maybe you don't have to update everything - it sounds like there might be a lot of updating going on, so if it's too inefficient you might want to look at a better approach.
It's a bit difficult to know without more info but lets say, for example, you have a text box that triggers a worksheet calculate and you want to update an individual label from a specific range... if you enter that specific range in the textbox's "Tag" property, you could use something likePrivate Sub TextBox1_AfterUpdate()
Label1.Caption = ActiveSheet.Range(TextBox1.Tag).Text
End SubThe tag property is a string, so if you had multiple updates from one AfterUpdate event, you could enter comma-delimited ranges and split them into an array

SailFL
09-01-2005, 09:37 AM
This sounds interesting. I looked at the info with Basic help and I didn't get much help or I don't understand what I was looking at.

Can you provide some more details to your example?

Thanks for the additional thinking about my problem. I am currently analyzing the calculations to see what updates what so I can see what exactly has to be updated.

I would like to understand you suggestion.

Killian
09-01-2005, 11:52 AM
I've attached a simple example. Hope it helps.

SailFL
09-01-2005, 12:23 PM
Thanks, I will have to look at the code some more. I beliefly looked at and it works pretty neat.

I might be able to use it!

I looked at the code more and you are going to make me work. I very much like what you sent me and I will have to play with it some more. I think I will be reworking the way I have done some things.

Killian
09-01-2005, 04:33 PM
I looked at the code more and you are going to make me work... I think I will be reworking the way I have done some things.Glad it's useful... sounds like there might be a little restructuring going on - with a lot of form controls its best to spend some time planning things on paper. If you can work out a logical system using the tag property and the names of the controls you might also be able to instance all your textboxes as a custom class so you only have one event - I'll try to post an example of that soon

SailFL
09-01-2005, 06:10 PM
Now you tell me all this good stuff when I am getting to the point that I am close to being done. But I liked your idea and I see the advantage. I look forward to your next handy code. I don't mind redoing if it will make the code more efficient and easier to work with. I have a lot to learn.

Thanks for the input and the time you are taking.

Killian
09-02-2005, 08:25 AM
OK, here's the other approach. With this, a bit of organization and preparation means you can use the same few line of code for all the events so maintanence is easier.
It works like this - When you add textbox, you set its tag property to the same range you want it to update. Likewise, a label has its tag property set to the range you want it to read.
The the form is initialized, all the textboxes are set as members of a custom class which you enable events for. You then write ONE event routine that will fire when ANY of the textboxes on the form are changed. In the example, I've got a bunch of textboxes and equivalent labels. When any of the textboxes are changed, the worksheet is recalc'ed and all the labels are updated.

I had to use the Change event because when you create the custom class derived from a Textbox, After_Update isn't available (I haven't looked into why, I just decided to accept it and move on). This means there may be a lot of code firing on each keypress but remember, you don't have to add all the textboxes to your event-enabled class collection (perhaps just the ones whose Tag property isn't empty..?)

Hope I'm not creating too much work for you :whistle:

Enjoy

SailFL
09-02-2005, 12:13 PM
Killian,

Thanks for all your efforts. I have it downloaded and I am looking at it. It will take me some time to understand what you have done.

Thanks

SailFL
09-02-2005, 12:58 PM
Killian,

To be honest I may not be ready for your code.

I found two pages of code.. CFormTextBox and UserForm. The CForm is the class definition and the UserForm is the code for the form.

Where do I find information about MSForms?

I am going to have to play around with it because I am using textboxes that are using numbers only with different formats and some that are strings. I think I have to many special cases.

My OOP is rusty. Your code does use less code. I would have to instantiate some different children. ( I think that is what I mean).

Additional suggestions would be helpful. That is why I asked about the book. The book I have, Excel 2003 Power Programming with VBA, Walkenbach is beginner to Intermediate. I need a more advance book.

Thanks

SailFL
09-03-2005, 08:22 AM
Killian,

I did find some thing in the book I bought but I like what you sent better. I have included a file that I created to test. I will keep working on it but I would appreicate your suggestions. I would like to be able to update using the tag idea the TextBox with the myValue and to write it to the B1-B3 location on the spreadsheet.

Once I have this working, I will change the code on the application that I have been working on. I also have some other areas on my UserForm that the user inputs items that are the same for 12 items so I think I can do the same for them.

Thanks

Killian
09-03-2005, 10:11 AM
OK... well I've added a couple of lines to the class that update the sheet and the textboxes - I think that's what you meant...

It seems to work fine from what I understand - is the user going to input data into the textboxes as well? At the moment there's no events on them.
You also mentioned some textboxes working with specific number formats. Validation will not be possible using this class method since the Before_Update, After_Update and Exit events aren't available, but I'm sure we could work something out to keep things simple

SailFL
09-03-2005, 10:38 AM
Killian,

Thanks for the changes. I ran the new code and I like it. I will look over the code to better understand. In this example, the button is used to add the price of the paver. Currently there are two paver prices. So I wanted to see if I could write it and display also.

I think that with this information, I can use this method in anothe place though I do have some input formating and some data validation.

I will see if I can put together an example when I am ready.

Where can I find an explaination of this syntax:

m_Form.Controls("TextBox" & Right(TogButtonGroup.Name, Len(TogButtonGroup.Name) - 12)).Value = myValue

I can not find in the help that comes with excel or my book.

Thanks

Killian
09-03-2005, 12:13 PM
m_Form.Controls is the collection of controls and the normal syntax would be to select the control by name, e.g:
m_Form.Controls("TextBox1").Value = myValue
to assign the variable myValue to the value of that control

But because we're working from a class, we don't know which textbox we want to update, but if TextBox1 is updated by ToggleButton1, TextBox2 is updated by ToggleButton2, and so on, we can create the name by stripping the "ToggleButton" part off the name and adding the remaining number to "TextBox".
The Right function returns a string containing a specified number of characters from the right side of a string.
Syntax: Right(string, length)
You might get away with using 1 for the length part of that unless you have more than 9 controls, so I used the Len function as well to return the total number of characters in the name and take off the 12 characters in "ToggleButton"

Hence the name of the control we want is:
"TextBox" & Right(TogButtonGroup.Name, Len(TogButtonGroup.Name) - 12)

SailFL
09-03-2005, 01:04 PM
Excellent! That explains were the 12 came from. Is there another source were I can go to read?

You are a lot of help and I appreciate the input.

SailFL
09-04-2005, 03:21 AM
Killian,

Here is a simplified verson of my UserForm. It is actually one page with a made up version of the spreadsheet.

I need help with SqFtCalc class. I am not asking you to write all the code but I need some help with what kind of Event am I going to use.

This class will allow SqFt input to modify the the spreadsheet which will than appear on the form. This is a very simple demo for me. I figure if I can do this I will begin to learn how to add the more difficult parts.

I am a write and test kind of guy. Start simple and test. If the simple stuff will work the more complicated will also.

Thanks

Killian
09-05-2005, 10:09 AM
Well the only option is to use the Change event with this method.
I got row 1 on your form working by making a couple of code changes (I put in comments to mark where). I set the textbox "SqFt1" tag property to "B2" and formatted all the cells in column B below the header to "#0.00" (no need to do that in code).
It seems to work.

Just as a thought, while I was looking at this, it occurred to me that there might be an easier way to manage this - using a listbox for all the data and editing one entry at a time, so you only have one set of controls for the user to edit. I added a rough example of what I think you want as UserForm2

SailFL
09-06-2005, 12:12 AM
Killian,

Thanks for the optional Listbox suggestions but I think I am at this point going to stay with my UserForm.

I am missing how to get the changes from the spreadsheet(SS) to my form. Since the price is calculated by SS, I just need to display it on the form.

I looked at your TextBox1_Change in the UserForm. So I tried something like

Private Sub Price1_Change()

Price1.Value = ActiveSheet.Cells(Price1.Tag).Value
End Sub

But this is not correct because it did not work. I believe I have the reference to the cell incorrect.

This would be in the UserForm code or would it be placed in the workbook because I am interested in when the sheet changes and have it trigger from there?

Okay, I came to a realization while creating this memo but I need some direction. The code will not work in the UserForm because the price in the userform never changes unless I have code that updates when the sheet changes.

How do I do this?
Thanks - I am learning a lot with your help and direction.

Killian
09-06-2005, 01:49 AM
Well I suppose the other thing we could do here is consider the use of the WorkSheet_Calculate event. From what I can tell, in all cases, the code needs to do the same thing:

1. apply user input from form to worksheet
2. calculate worksheet
3. update form

So that would give us:
1. use textbox events to apply values to worksheet
2. use ActiveSheet.Calculate at the end of each event
3. update your total with
Price1.Value = ActiveSheet.Cells(Price1.Tag).Value
in the WorkSheet_Calculate event

SailFL
09-06-2005, 03:56 AM
I need a little more direction. Where do I put this code and is there more than this.

Thanks

Private Sub Worksheet_Calculate()
Price1.Value = ActiveSheet.Cells(Price1.Tag).Value
End Sub

Killian
09-07-2005, 01:28 AM
In the VBE project explorer, there are some expanding folders: Microsoft Excel Objects, Forms (if present), Modules (if present), and Classes (if present).
Just as you access the code for the forms, modules and classes, you can add code for the WorkBook and Sheets. Double-click the appropriate sheet icon and in the dropdowns above the code window you can select (left) the general declarations and worksheet areas, With worksheet selected, the sheet events will be available in the right dropdown menu. There are a few to play with - you should also check out the workbook events, some are very useful!
Oh, and you'll need to refer to the textbox explicitly since you won't be in the userform code module UserForm1.Price1.Value = ActiveSheet.Cells(UserForm1.Price1.Tag).Value

SailFL
09-07-2005, 02:50 AM
Killian,

I did place the code in the worksheet area. When I use the above code, I get a type mismatch. I must admit I am lost...... could you change the previous code that I sent you to show me what I am doing wrong. Thank you.


I was also thinking, can't I do the same thing that I did with the SqFt and the togglebutton. In the above case I am giving it the explicit name of the textbox that I want to have updated, Price1. Can't I have it update all and only the once that change by using the Tag reference and the Spreadsheet knows that it changed? So that all the values that change will be reflected in the Userform and do it for one and they all are replicated when I create new ones.

Is that possible?
Thanks and thanks for your patients!!!!!:clap:

Killian
09-07-2005, 03:24 AM
Well you can (by looping though all the control in the form and updating the ones you want) but I'm not clear on why you would want to do that if your following the sequence of events in post #21 (http://www.vbaexpress.com/forum/showpost.php?p=42590&postcount=21) .
Apart from anything else, that would create an infinite loop, wouldn't it??? When you update the control that has it's change event enabled, that event would fire, and so on...

SailFL
09-07-2005, 03:27 AM
Please look at the prevous post again. I added some comments. You are toooooo quick.

Thanks

Killian
09-07-2005, 04:15 AM
oops... my bad, you just need to change ActiveSheet.Cells(... to ActiveSheet.Range(... :doh:

I still don't get your second point :think: the sqft and toggle events update the sheet, the sheet event updates the controls on the form that read from calculated cells - everything should be correct at that point, no?

SailFL
09-07-2005, 04:52 AM
I am at a tender place here... Learning and trying and not understanding plus my ADD(or like Attention Deficent Disorder) is working against me.... so some times I need to see it completely to get it. And do I still put this in the sheet code area?


ActiveSheet.Price1.Value = ActiveSheet.Cells(ActiveSheet.Range(Price1.Tag).Value



Second point:
What I mean is that I explicitely provide the Price1 textbox. Why can't I tell it to use the tag reference and it would know which Price it needs to update like we are doing with the SqFt TextBox and ToggleButton. I am going to need to do this with all the input info for each line item on the UserForm. I will want to be able to do this with all my pages on my UserForm. I don't want to explicitly write code for each item.

Does that make better sense?

Thanks

Killian
09-07-2005, 05:23 AM
Ahh... no, I was being lazy, I meant that I used
ActiveSheet.Cells(UserForm1.Price1.Tag).Value
and i should have used
ActiveSheet.Range(UserForm1.Price1.Tag).Value

So, in the sheet code area, we're going to use the Worksheet_Calculate event to update certain controls on the form. ThisUserForm1.Price1.Value = ActiveSheet.Range(UserForm1.Price1.Tag).Valuewill get the value for the control "Price1" and assign it to the value for the range specifed by Price1's tag.

Perhaps it would be simpler to update the values for all the "Price" total textboxes in one go on that Worksheet_Calculate event?

To do that, we'll need to indentify each Price textbox and find which worksheet cell it reads from (by setting that range in the textbox's tag property as before)
So, in design mode on the userform, for each of the Price total textboxes, change the names to "Price1", "Price2", "Price3", etc and for each one, change the Tag property to reflect which cell that textbox reads ("F2", "F3", "F4"). Then you change the Worksheet_Calculate event to look like this Private Sub Worksheet_Calculate()

Dim ctrl As MSForms.Control

For Each ctrl In UserForm1.Controls
If Left(ctrl.Name, 5) = "Price" Then
ctrl.Value = ActiveSheet.Range(ctrl.Tag).Value
End If
Next

End Sub

SailFL
09-07-2005, 06:56 AM
This code is over writing the excel F2 location when it is complete. The formula at the F2 locations is replaced with the value which is not good. It would seem that this code should be getting the value based on the Tag and placing it in the UserForm1.Price1.Value.

I did a findit on Price1 and the only place I could find Price1 is in the worksheet where the code is.

I can't have it overwriting the formula.

Any suggestions?



Private Sub Worksheet_Calculate()
UserForm1.Price1.Value = ActiveSheet.Range(UserForm1.Price1.Tag).Value
End Sub

Killian
09-07-2005, 08:47 AM
This code is over writing the excel F2 location when it is complete.I don't see why it should, the statement simply assigns the range valu(based on the control tag) to the textbox value
I did a findit on Price1 and the only place I could find Price1 is in the worksheet where the code is.Thats the only place it is, aprat from the Name property of that textbox

I've updated the last one you sent, renamed all the textboxes, added the appropriate range in the tags and it seems to be updating fine. :dunno
There's still quite a lot to do, in terms of populating the initial data etc
Are yo sure yo don't want to use a listbox and one set of controls? :whistle:

SailFL
09-07-2005, 09:35 AM
Please send me a copy of the code. I would like to see what I am doing wrong.

No, I am down the path of a UserForm and I want to continue.

Thanks

Killian
09-07-2005, 10:52 AM
:doh: forgot to attach it

SailFL
09-07-2005, 11:10 AM
Killian,

That is slick. Did you try doing it the other way with just the statement for the one field.

I will look over the code and start working on formating and changing the other areas that I want to see work. I am sure I will have other issues but you have been a great help and have hung in there with me and I appreicate it very much.:bow: I like the way this is working because it uses so much less amount of code than what I was generating and should be easier to add to the form and little or no code should have to be added.

I hope I can help others....... the way you have helped me.

I will be in touch and hopefully soon.

Killian
09-08-2005, 03:58 AM
Glad to hear its working out :thumb

I'll mark the thread as solved - You can always link back to it from any future threads for reference if you need to

Look forward to hearing from you :hi:

mdmackillop
07-08-2009, 12:44 AM
What happened to the attachments here?