Subtracting text boxes from one another doesn't work if answer is negative.

I have two text boxes that are set to deduct from one another on the user form when the user presses the submit button.
If the value in the second text box is less than the value in the first giving a positive answer then it works fine but if the answer is negative then no answer is produced!

The code for my deduction is:

'Calculate Loss/Gain Text Box
        Me.LossGainTextBox.Value = "invalid"
        Me.LossGainTextBox.Value = Val(Me.TargetRateTextBox.Value) - Val(Me.RateTextBox.Value)

Once the submit button is pressed the answer from this subtraction should be put into a particular cell on the spreadsheet and my code for this is:

ActSheet.Cells(emptyRow, 140).Value = Format(Me.LossGainTextBox, "$#,##0.00")

Any help would be greatly appreciated.

Many Thanks

Thanks for the response, I did not know Val() was a string function and could be problematic, i've changed a lot of code which I had used this.

Unfortunately although better code it has not solved my issue of receiving blank results if the answer to the sum is a negative one!

Is there anything else I can check or do I need to use a different formatting code for the box for when the result is filled into the spreadsheet?

I have googled a lot to try and solve this from other peoples learnings but I cannot find anyone else with the same problem!

Many thanks for any other help you can offer.

Thankyou for your responce,
The output cell is within a table and simply displays nothing! But if the result is positive it shows a value as i would expect.

Possibly there are issues where the numbers come from that go into the code if it works fine for you.

The TargetRateTextBox is different dependant on the selection made by the user in the userform from a combobox (GenreSubSectionCBO), the Jobsummary worksheet is where the sheet administrator types the target rates that populate the userform.
The TargetRateTextBox is populated from the worksheet dependant on a selection using the code;

Private Sub GenreSubSectionCBO_Change()
    Dim JobSummaryWS As Worksheet
    Set JobSummaryWS = Worksheets("JobSummary")
		If Me.GenreSubSectionCBO.Column(0) = JobSummaryWS.Cells(19, 3) Then Me.TargetRateTextBox.Value = JobSummaryWS.Cells(19, 5)
		If Me.GenreSubSectionCBO.Column(0) = JobSummaryWS.Cells(20, 3) Then Me.TargetRateTextBox.Value = JobSummaryWS.Cells(20, 5)
    	If Me.GenreSubSectionCBO.Column(0) = JobSummaryWS.Cells(21, 3) Then Me.TargetRateTextBox.Value = JobSummaryWS.Cells(21, 5)
    	If Me.GenreSubSectionCBO.Column(0) = JobSummaryWS.Cells(22, 3) Then Me.TargetRateTextBox.Value = JobSummaryWS.Cells(22, 5)
    	If Me.GenreSubSectionCBO.Column(0) = JobSummaryWS.Cells(23, 3) Then Me.TargetRateTextBox.Value = JobSummaryWS.Cells(23, 5)
    	If Me.GenreSubSectionCBO.Column(0) = JobSummaryWS.Cells(24, 3) Then Me.TargetRateTextBox.Value = JobSummaryWS.Cells(24, 5)
    	If Me.GenreSubSectionCBO.Column(0) = JobSummaryWS.Cells(25, 3) Then Me.TargetRateTextBox.Value = JobSummaryWS.Cells(25, 5)
    	If Me.GenreSubSectionCBO.Column(0) = JobSummaryWS.Cells(26, 3) Then Me.TargetRateTextBox.Value = JobSummaryWS.Cells(26, 5)
    	If Me.GenreSubSectionCBO.Column(0) = JobSummaryWS.Cells(27, 3) Then Me.TargetRateTextBox.Value = JobSummaryWS.Cells(27, 5)
    	If Me.GenreSubSectionCBO.Column(0) = JobSummaryWS.Cells(28, 3) Then Me.TargetRateTextBox.Value = JobSummaryWS.Cells(28, 5)

Pic of worksheet where target rate is initially inputted by administrator;
Screen Shot 2012-07-17 at 08.57.18.png

The RateTextBox code is;

    Me.RateTextBox.Value = CDbl(CostTotalsTextBox.Value) / CDbl(ActProductionTextBox.Value)

The Cost Totals Text Box code works out all machine and labour costs minus any deductions.
The Activity production is a user typed textbox which i have tried to make stronger code as suggested in the first reply which told me to make sure the user inputs a numeric value with the if IsNumeric code, when i have applied this to my original code I get a Run-time error 13 'Type Mismatch'.
This is the code as advised;

	If IsNumeric(ActivityProductionTextBox.Value) Then
        Me.RateTextBox.Value = CDbl(CostTotalsTextBox.Value) / CDbl(ActProductionTextBox.Value)
    Else
        MsgBox "The Activity Production Box must be numeric, please correct"
        MultiPage1.Value = 0
        Me.ActivityProductionTextBox.SetFocus
    Exit Sub
End If

Sorry I know this is lots of info and probably makes more sense to me than to anyone else not working on my project, if you need any more info to help me then please ask.

Many Thanks,

'Calculate RateTextBox
MsgBox "Unformatted value: " & Me.LossGainTextBox & vbCr & "Formatted value: " & Format(Me.LossGainTextBox, "$#,##0.00")
ActSheet.Cells(emptyRow, 140).Value = Format(Me.LossGainTextBox, "$#,##0.00")

It says
Unformatted Value:
Formatted Value:

Screen Shot 2012-07-17 at 12.09.36.png

When I apply this code both message boxes show the result that it should be e.g.; 1 - 100 = -99 but it still shows up blank when being transferred to my spreadsheet.

Screen Shot 2012-07-18 at 14.49.52.png

I have checked the formatting of the cells on the worksheet and set them all to currency, is there anything else i should do to the sheet itself?

This problem makes no sense to me because the message boxes show that the calculation is fine and makes no sense that it only transfers data to the sheet if the result is over 0!

Thank-you for sticking with me on this and helping out.

I have tried working through this and really cannot see the problem so please find attached my spreadsheet, I have deleted most un-relevant content so that it is a size that is sendable, if you want to run the user form you do it from the 'instructions, input & print' page; choose 'Clearing Job Sheet' then from the 'Sub Section' combo box drop down choose the first option 'test subsection 1', add a small number into the 'Activity Production' text box then go onto the labour tab and put any number in the 'total man hours worked' text box.
It will also require a date entry.
The Combobox will make the data go onto the 'CLS1' sheet.
The values will make the rate higher than the target rate causing a negative number.

Thanks again,
Ben

I've cleared the tables on sheet 'CLS1' then inputted two sets of data into the user form, 1 that results in a positive result ($0.50) and one that should have resulted in a negative result (-$11.50), as you can see from my print screen picture the pink loss/gain column only shows the positive result.

Screen Shot 2012-07-18 at 22.00.25.png

Was this not the case on your computer, should i re-install Office? Have you ever seen problems like this running Office on a Mac?

I wasn't sure and assumed it was 2010 when filling in my profile. I am running 2011 for the Mac.

thank-you again for your time and help.

Does this mean that code within VBA acts differently on different versions of excel that will eventually open the workbook?

That works well now, I'll have to incorporate some code to find out the platform and version then activate different code from this.

Thanks again for all your help.