+ Reply to Thread
Results 1 to 3 of 3

update cell/cells after updating a textbox on a userform

Hybrid View

  1. #1
    Registered User
    Join Date
    10-30-2014
    Location
    Greenville, SC
    MS-Off Ver
    2010
    Posts
    2

    Smile update cell/cells after updating a textbox on a userform

    Sheet name 'Inventory'

    _______A________________B
    1
    2
    3
    4
    __Model number__________In Stock__________________'Update Inventory' command button to open userform
    5__239-283-1_______________9
    6__172-384-2_______________10
    7__123-284-9_______________7

    Just a cut out of the whole list. Below I'll have my code til now. I'm looking to update items in stock from my userform either
    adding and/or subtracting from the number in stock(numavail).
    If the cell 'In Stock' < 6 items change the background color of the cell to Yellow and if < 2 background color to red


    When the userform is initialized
    Private Sub UserForm_Initialize()

    Dim lastRow As Long, cboCell As Range
    'Combo box is populated with the range in Row A
    With cboModelNumbers

    contain the last row in column A
    lastRow = Sheets(Inventory).Cells(Rows.Count, 1).End(xlUp).Row

    'A for loop through each cell in Row A from A5
    For Each cboCell In Range("A5:A" & lastRow)
    'number 1 cont'd. If statement to test if cells have something in them
    If Len(cboCell) > 0 Then cboModelNumbers.AddItem cboCell.Value
    Next cboCell

    'Combo box will have first/top model number visible in the combo box
    cboModelNumbers.ListIndex = 0
    End With
    End Sub
    'When the combobox has a change
    Private Sub cboModelNumbers_Change()

    'Preset numsold and numacquired text boxes to 0 and UpdateAm to same as numavail
    txtNumSold.Value = 0
    txtNumAcquired.Value = 0
    txtUpdatedAm.Value = txtNumAvail.Value

    'Whatever model number is selected from the combo box the number available for that model number will appear
    Me.txtNumAvail.Value = Application.VLookup(Me.cboModelNumbers.List(Me.cboModelNumbers.ListIndex), _
    Range("A5:B21"), 2, False)

    End Sub
    'When the command button 'UpdateStock' is clicked
    Private Sub cmdUpdateStock_Click()

    txtUpdatedAm.Value = txtNumAvail.Value - txtNumSold.Value + txtNumAcquired.Value

    ' and if statement to not allow the user to sell more than the available amount
    If Val(txtNumSold.Value) > Val(txtNumAvail.Value) Then
    MsgBox "You are unable to sell that many items for this model number"
    'Reset the number in the sold text box to 0
    txtNumSold.Value = 0
    txtNumAcquired = 0
    txtUpdatedAm.Value = txtNumAvail.Value
    End If

    'If the udpated amount is less than 0 without the sold amount being greater than the number available
    If Val(txtUpdatedAm.Value) < 0 Then
    MsgBox "You have tried to sell too many items", , "We don't have that many items to sell"

    'Reset the number sold and acquired text boxes to 0
    txtNumSold.Value = 0
    txtNumAcquired.Value = 0
    txtUpdatedAm.Value = txtNumAvail.Value
    End If

    End Sub

    Private Sub cmdCancel_Click()
    Unload.me
    End Sub

  2. #2
    Forum Contributor
    Join Date
    04-14-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    198

    Re: update cell/cells after updating a textbox on a userform

    Use [CODE tags for the code you ahve provided /CODE]

    If you go advanced to reply you can attach your file so we can help you much easier.

  3. #3
    Registered User
    Join Date
    10-30-2014
    Location
    Greenville, SC
    MS-Off Ver
    2010
    Posts
    2

    Re: update cell/cells after updating a textbox on a userform

    Below is the attachment. the line I'm looking for has '?????
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Userform textbox to update column (comment) ?
    By waihar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-08-2013, 10:55 PM
  2. [SOLVED] Run Time error 91 while updating texts in textbox in userform
    By Cijo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-25-2012, 09:08 AM
  3. Use textbox input and VLookup to update another textbox on same userform
    By gcoug in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-22-2011, 10:39 AM
  4. Userform Textbox updating a another userform textbox (im stuck!!)
    By wapwap in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-31-2011, 02:56 PM
  5. Updating Textbox in UserForm
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-03-2009, 01:55 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1