textbox controlSource problem

[moa]

Hello,

I'm builing a form for user input and displaying totals. I'm using textboxes (flat and transparent to look like labels) to display the totals and using their controlSources to link to cells on different sheets that have (mostly) SUM formulas in them. Unfortunately it doesn't seem to work as the cells' formulas disappear leaving only the value when I open the form. I don't want this to happen.

I assume that the cell is taking the textbox value but the textbox also seems to take the cell value. Seems weird. Is there a work around or should I just go with label captions to display totals?

[Steiner]

ControlSource indeed works in both directions. You could remove the ControlSource from the Sum-Boxes and try to find another way to recalculate the Sum-Boxes.

I'd try the Worksheet_Calculate event, as this one is fired every time you change a value on your form that is linked to a cell.

Daniel

[Bob Phillips]

Glen,

If you want to stick with the userform, just load the combobox from the range, rather than pointing at it with ControlSource.

BTW, you sure are confused with your flags :)

[moa]

Cheers for the suggestions guys.

Bob, it's a textbox.


just load the combobox from the range

Did you mean using the rowsource of a combobox instead of controlSource or something else that might work with a textbox?


BTW, you sure are confused with your flags :)

true, I'm feeling homesick for NZ. Must get out of London.

[Bob Phillips]

Bob, it's a textbox.

Did you mean using the rowsource of a combobox instead of controlSource or something else that might work with a textbox?

It's the same principle.

What I mean is rather than set the Controlsource property, load it in the Userform_Activate event, like so



Textbox1.Text - Worksheets("Sheet1").Range("A1").Text


a bit more work, but stops the two way flow.


true, I'm feeling homesick for NZ. Must get out of London.

My daughter is English, in Poland at the moment, but also missing NZ. She goes back in April, and will get her residency then. Never been, but everyone seems to love it. North or South Island?

[moa]

What I mean is rather than set the Controlsource property, load it in the Userform_Activate event

oh bugger, thought you meant there was an easy/lazy way. I was trying to avoid that as there are quite a few of them.


North or South Island?

I've lived all over that little place. Hamilton, Auckland, Dunedin, Wellington.

Don't go to Hamilton if you go to NZ. Best place is the Coromandel Peninsula in the North Island during summer (now) and Wanaka (and surrounds) in the South Island during winter IMHO.

[Charlize]

When you've got 3 textboxes and values are in A1, A2 and A3. Textboxes are named textbox1, textbox2 and textbox3. If cell is different, let's say, beginning on A5, use a second counter inside the loop of i and add 1 to this counter.
Private Sub UserForm_Activate()
Dim i As Long
For i = 1 To 3
Me.Controls("TextBox" & i).Value = Range("A" & i).Value
Next i
End SuborPrivate Sub UserForm_Activate()
Dim i As Long
Dim inside_i As Long
inside_i = 5
For i = 1 To 3
Me.Controls("TextBox" & i).Value = Range("A" & inside_i).Value
inside_i = inside_i + 1
Next i
End Sub

[Bob Phillips]

I've lived all over that little place. Hamilton, Auckland, Dunedin, Wellington.

Don't go to Hamilton if you go to NZ. Best place is the Coromandel Peninsula in the North Island during summer (now) and Wanaka (and surrounds) in the South Island during winter IMHO.

Just heard from my daughter, she has been granted residency. Wanaka is where she lives, looking to start a sport holiday business.

[moa]

Hey that's great or "sweet as" as some kiwis say. Hope your daughter does well. There are a lot of well-to-do tourists in Wanaka. Some pretty flash Holiday homes were going up when I was there last so it's not exactly the private playground it used to be but the scenery is fantastic and of course a sport holiday business would be very lucrative. You should go visit her when she's set up.

[moa]

actually Charlize that is exactly my plan. right in the middle of doing that already. I have a hundred or so labels to name so I can loop through them and display the totals I need to.

[Bob Phillips]

Hey that's great or "sweet as" as some kiwis say. Hope your daughter does well. There are a lot of well-to-do tourists in Wanaka. Some pretty flash Holiday homes were going up when I was there last so it's not exactly the private playground it used to be but the scenery is fantastic and of course a sport holiday business would be very lucrative. You should go visit her when she's set up.

I agree, it looks fantastic, couple nice walking trails nearby, and Milford Sound, not to mention Mount Cook.

It is the plan to go visit as soon as she gets a house.

So that is where she picked up "sweet" from :).