PDA

View Full Version : UserForm & Spreadsheet updating problem (I am Back)



SailFL
09-11-2005, 12:58 AM
Killian,

I have another problem......

I don't want the price or labor cost to show on the form unless there is a sqft value. So how I can I check the excel spreadsheet cell for a sqft value and if it has a value than go ahead and display the price and labor cost.

Or would it be better to create a an array of of flags. When a SqFt was given a value the flag would be true for that item and use that to determine if the to display the corresponding price and laborcost. But I would rather do it off of the spreadsheet.

I am open to suggestions, please.

Thanks



Private Sub Worksheet_Calculate()
Dim ctrl As MSForms.Control
' If the Labor Cost change, display new labor cost
' If the price changes, display new price
For Each ctrl In UserForm1.Controls
' Here I want to check the value of SqFt to determine if I am going to display
' the values for Labor Cost and Price????
If Left(ctrl.Name, 9) = "LaborCost" Then
ctrl.Value = ActiveSheet.Range(ctrl.Tag).Value
End If
If Left(ctrl.Name, 5) = "Price" Then
ctrl.Value = ActiveSheet.Range(ctrl.Tag).Value
End If
Next
End Sub

royUK
09-11-2005, 03:40 AM
Does this help?


Dim ctrl As MSForms.Control

''' If the Labor Cost change, display new labor cost
''' If the price changes, display new price
For Each ctrl In UserForm1.Controls

''' Here I want to check the value of SqFt to determine if I am going to display
''' the values for Labor Cost and Price????
If ActiveSheet.Range(sqft).Value > "" Then 'correct the range

If Left(ctrl.Name, 9) = "LaborCost" Then
ctrl.Value = ActiveSheet.Range(ctrl.Tag).Value
End If

If Left(ctrl.Name, 5) = "Price" Then
ctrl.Value = ActiveSheet.Range(ctrl.Tag).Value
End If
Next
Else: MsgBox "No sq ft value"

SailFL
09-11-2005, 05:22 AM
That will not do the trick but thanks for you input.

royUK
09-11-2005, 05:38 AM
I can't help if you don't elaborate - why won't it work/ it checks if the range(when you amend it to suit" has a value if it does it continues your code, if not exits

SailFL
09-11-2005, 06:05 AM
royUK,

Go look at the previous sloved with the same title and place the code in the appropriate place and you will see that it will not work and understand the problem.

Thanks

royUK
09-11-2005, 06:28 AM
I am not sure what this Forum does about this situation, but I would have thought it more useful to at least post a link to your previous Thread.

SailFL
09-11-2005, 07:39 AM
Well I fixed my own problem. Here is my fix.



Private Sub Worksheet_Calculate()
Dim ctrl As MSForms.Control
Dim rangeTarget As Range
' If the Labor Cost change, display new labor cost
' If the price changes, display new price
For Each ctrl In UserForm1.Controls
If Left(ctrl.Name, 9) = "LaborCost" Then
Set rangeTarget = ActiveSheet.Range(ctrl.Tag)
If rangeTarget.Offset(0, -3).Value > 0 Then
ctrl.Value = ActiveSheet.Range(ctrl.Tag).Value
End If
End If
If Left(ctrl.Name, 5) = "Price" Then
Set rangeTarget = ActiveSheet.Range(ctrl.Tag)
If rangeTarget.Offset(0, -4).Value > 0 Then
ctrl.Value = ActiveSheet.Range(ctrl.Tag).Value
End If
End If
Next
End Sub


Yes I should have added a link but I don't know how!!

I have the range value set statically. Is there a way of doing this dynamically?????



If rangeTarget.Offset(0, -3).Value > 0 Then


Thanks for the input.

Airborne
09-11-2005, 03:28 PM
Hi SailFL,

Yes I should have added a link but I don't know how!!


Go to thread you want to link, right click on it, a pulldown menu will show up, choose Copy Shortcut, submit your reply and paste the shortcut in there
Not sure if this is the best way to create a link in a message but it works for me.http://vbaexpress.com/forum/images/smilies/045.gif

A link to your previous Thread.
http://www.vbaexpress.com/forum/showthread.php?t=4958


Regards,
Rob.

SailFL
09-11-2005, 04:28 PM
Thanks for the information on adding previous threads.