Solved: UserForm & Spreadsheet updating problem

[SailFL]

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]

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]

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]

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 like[VBA]Private Sub TextBox1_AfterUpdate()
Label1.Caption = ActiveSheet.Range(TextBox1.Tag).Text
End Sub[/VBA]The 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]

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]

I've attached a simple example. Hope it helps.

[SailFL]

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]

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]

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]

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

Enjoy

[SailFL]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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
[VBA]
Private Sub Price1_Change()

Price1.Value = ActiveSheet.Cells(Price1.Tag).Value
End Sub
[/VBA]
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.