Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    + / - Cash on a User form (2003 SP2)

    Good morning

    A friend of mine is a gambler (first mistake) and he has asked me to compile a workbook (second mistake <img src=/S/grin.gif border=0 alt=grin width=15 height=15> ) of his winnings and losings, I thought that I could adapt the stock sheet I have been working on but I again run into the problem of - values, if I can tell you what I have done so far you will hopefully realise what I am trying to do.

    I have made a kind of petty cash jornal on a WS with (a) bet details, ( date, bet amount (d) won (e) lost (f) running total - there is a seperate cell with the opening balance from which column f derives its value along with calculations from the bet the won (d) and lost (e) columns.

    On the User Form I have the following fields

    txtBetDetails - updates A
    txtDate - Updates B
    txtBetAmount - Updates C
    txtAmountWon - Will update D
    TxtAmountLost - Will update E

    Premliminary forays have worked OK except with my old friend the negative amount txtAmountLost, I have column e formatted as number <font color=red>-1234 </font color=red> but searching the forum and Googling I cannot for the life of me find anything that I can use as code in the txtAmountLost field to say "this is a - amount"

    Any thoughts please

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: + / - Cash on a User form (2003 SP2)

    Hi Steve:

    Just a brief thought; how about

    TxtAmountLost = -TxtAmountLost

    unless I have misunderstood your dilema?
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: + / - Cash on a User form (2003 SP2)

    Thanks for the response Tony

    I am very inexperienced with VBA and have tried

    a. Placing it 'as is' in my existing code
    b. trying to Dim the txtBox as but it does not like it
    c. Me. but it does not like that either

    Can you (politely) tell me were to stick it and how ( editted because double 'were')

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: + / - Cash on a User form (2003 SP2)

    Why do you want column E to be negative numbers? (Although your friend might like it - if you lose a negative amount, you actually win <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    I'd simply store positive amounts in columns D and E, and take this into account in the formulas that calculate the balance: add the column D amounts, subtract the column E amounts.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: + / - Cash on a User form (2003 SP2)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Oops!!

    Your right I should have seen that myself, I have just become obssesed with text box values I think

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: + / - Cash on a User form (2003 SP2)

    Hi Steve:

    Hans is, of course, quite right - when isn't he!!

    However, if you do want to negate the value of a text box, I attach a (very) simple form which does so. You will have to CLICK OKAY. The negation takes place in the code behind the Okay Button.

    Regards,
    Attached Files Attached Files
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: + / - Cash on a User form (2003 SP2)

    Hi Tony

    Thank you for that it will come in extremely useful for another workboook that I am working on if I can fathom out how to incorperate it into existing code, it will make the form even more warehouse staff staff proof.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: + / - Cash on a User form (2003 SP2)

    Hi Tony

    Try as I might I cannot get that to work among this bit of code

    Private Sub UserForm_Initialize()
    CboCountry1.List = Range("Countries").Value
    cboStock1.List = Application.Transpose(Range("PackingMaterial").Val ue)
    End Sub
    Private Sub CmbRemoveStock_Click()
    Dim r As Long
    Dim c As Long
    Dim wsh As Worksheet
    Dim rng As Range

    Set wsh = Worksheets("StockControl")
    With wsh
    r = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
    .Cells(r, 1) = Me.CboCountry1
    Set rng = .Range("PackingMaterial").Find(What:=Me.cboStock1, LookIn:=xlValues, LookAt:=xlWhole)
    If rng Is Nothing Then
    MsgBox "Can't find cell for " & Me.cboStock1, vbExclamation
    Exit Sub
    End If
    c = rng.column
    .Cells(r, c) = Me.TxtQuantity1.Text

    End With

    Me.CboCountry1.Value = ""
    Me.cboStock1.Value = ""
    Me.TxtQuantity1.Value = ""
    Me.CboCountry1.SetFocus

    End Sub
    Private Sub TxtQuantity1_KeyPress(ByVal KeyAscii _
    As MSForms.ReturnInteger)
    Select Case KeyAscii
    Case 46
    KeyAscii = 0
    Case 45, 48 To 57
    Case Else
    KeyAscii = 0
    End Select
    End Sub
    Private Sub cmbClose_Click()
    Unload Me
    End Sub

    Can you help me and tell me how to incoperate the - bit into the TxtQuantity1 field?

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: + / - Cash on a User form (2003 SP2)

    This will put the negative of the txtquantity1 into the cell:
    <pre>.Cells(r, c) = <font color=red>-</font color=red>Me.TxtQuantity1.Text</pre>


    Is that what you are after?

    Steve

  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: + / - Cash on a User form (2003 SP2)

    Perfect Steve

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •