Help Geoff 1

[APElliott]

Sorry to be a nuisance Geoff!

That code is working fine now, just 1 problem the currency format for some of the values as been lost.

How do I fix this?

Thanks

Andrew

 

[xlbo]

Here ya go
Private Sub UserForm_Activate()
TextBox1.ControlSource = ActiveSheet.Name & "!F3"
TextBox1.Value = Format(ActiveSheet.Range("F3&quot.Value, "currency&quot
End Sub Rgds
~Geoff~

 

[APElliott]

Cheers Geoff,

I've come across another problem with this though!

The value in cell f3 is =sum(f8:f1000).

I run the userform that displays this value, but once open the value replaces the formula, and therefore doesn't update should any of the values below change.

Any suggestions!

Thanks

Andrew

 

[xlbo]

Andrew - how do the values in the worksheet change if the userform is open - are you making changes to the worksheet via the userform ?? if so, how is it done (button click or cell link)

What you are asking for is possible, it just depends on the rest of your processes as to how it can work - bit of info regarding how the form is used would be good....
Rgds
~Geoff~

 

[APElliott]

Hi Geoff,

I almost solved it before, but not quiet.

What i have are company names in cell d1 g1 j1 m1 p1 s1 and there quote values are summarised in cells f3 i3 l3 o3 r3 u3.

I have a userform that as command buttons to select the cheapest companies rates in cells e h and so on. These commands work fine, but i have another command that switches to this other userform that displays the values above rather that having scroll all over the screen.

This is how i have done it with A1 being the company trade ie Carpentry etc...

Private Sub UserForm_Activate()
TextBox1.ControlSource = ActiveSheet.Name & "!A1"
TextBox2.ControlSource = ActiveSheet.Name & "!d1"
TextBox3.ControlSource = ActiveSheet.Name & "!g1"
TextBox4.ControlSource = ActiveSheet.Name & "!j1"
TextBox5.ControlSource = ActiveSheet.Name & "!m1"
TextBox6.ControlSource = ActiveSheet.Name & "!p1"
TextBox7.ControlSource = ActiveSheet.Name & "!s1"
TextBox8.ControlSource = ActiveSheet.Name & "!f3"
TextBox9.ControlSource = ActiveSheet.Name & "!i3"
TextBox10.ControlSource = ActiveSheet.Name & "!l3"
TextBox11.ControlSource = ActiveSheet.Name & "!o3"
TextBox12.ControlSource = ActiveSheet.Name & "!r3"
TextBox13.ControlSource = ActiveSheet.Name & "!u3"

I did as you said and inserted that line to textbox8 to 13, but the problem occured that when the userform was run the formula changed to text.

So what i tried was to change your line from

TextBox8.Value = Format(ActiveSheet.Range("F3&quot.Value, "currency&quot

to

TextBox8.Value = Format(ActiveSheet.Range("F3&quot.Formula, "currency&quot

for textboxes 8 to 13.

This kept the formula in f3, but the formulae become text in the other cells.

If neccessary i'll have them unformatted, but if you can help it would be great.

Cheers,

Andrew

andyelliott@rolandbardsley.com

 

[xlbo]

Andrew - not quite seeing this clearly - if you want to, please send me your workbook (strip out any sensitive data if necessary) and I'll see if I can figure it out for you

If this is not possible, I would suggest that you change all your .controlsource 's to .text or .value - this should keep the formula in the worksheet cells if I am understanding you correctly Rgds
~Geoff~

 

[APElliott]

Geoff send me an email and i'll get it right over to you.

Cheers

Andrew

andyelliott@rolandbardsley.com