UserForm & Spreadsheet updating problem (I am Back)

[SailFL]

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]

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]

That will not do the trick but thanks for you input.

[royUK]

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]

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]

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]

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]

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.

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


Regards,
Rob.

[SailFL]

Thanks for the information on adding previous threads.