Userform to update sheet totals

L

Lynton

New Member
Joined
Mar 13, 2011
Messages
28
I have a simple spreadsheet that records electronic components in stock:
3 columns labelled Part Id: , description:, Stock.
I have created a user form With 2 text boxes Labelled, Part Id: and Quantity.
The user form has two Command Buttons Labelled Update and Close.
I need to Input data in to the user form (Part id and Quantity) and press the update button, It then should finds the correct part using the Unique part Id and add the quantity to the existing stock level.
Pressing the close button should close the data entry form.
any help appreciated.
Thankyou
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Hi
Have a look here: Create an Excel UserForm
Site provides step by step instructions for creating a userform for Parts Entry Database which you should be able to adapt to meet your specific project need & rather helpfully, you can download sample workbook for free.

Hope Helpful

Dave
 
Upvote 0
dmt32 said:
Hi
Have a look here: Create an Excel UserForm
Site provides step by step instructions for creating a userform for Parts Entry Database which you should be able to adapt to meet your specific project need & rather helpfully, you can download sample workbook for free.

Hope Helpful

Dave
Click to expand...
Thank you for you help. I have spent most of the weekend experimenting with this sample parts database. The problem I'm having is i don't want to create a new record in the database . I want to find an existing record and then ad the quantity to the existing stock level.
dmt32 said:
Hi
Have a look here: Create an Excel UserForm
Site provides step by step instructions for creating a userform for Parts Entry Database which you should be able to adapt to meet your specific project need & rather helpfully, you can download sample workbook for free.

Hope Helpful

Dave
Click to expand...
 
Upvote 0
Lynton said:
Thank you for you help. I have spent most of the weekend experimenting with this sample parts database. The problem I'm having is i don't want to create a new record in the database . I want to find an existing record and then ad the quantity to the existing stock level.
Click to expand...
 

Attachments

  • PartsDB.png
    PartsDB.png
    145.9 KB · Views: 9
Upvote 0
Hi,

Based on what you are seeking to do, then see if this will help you

Place ALL codes as published in your userforms code page

Code: 
Dim wsPartsData     As Worksheet

Private Sub cmdUpdate_Click()
    Dim strPrompt   As String
    Dim m           As Variant, PartNo  As Variant
    Dim StockQty    As Long, StockEntry As Long
    
    PartNo = Me.txtPartID
    If IsNumeric(PartNo) Then PartNo = Val(PartNo)
    
    StockEntry = Val(Me.txtQty)
    
    strPrompt = "Part No: " & PartNo & Chr(10)
    
    m = Application.Match(PartNo, wsPartsData.Columns(1), 0)
    If Not IsError(m) Then
        With wsPartsData.Cells(CLng(m), 3)
            StockQty = .Value
            If StockQty + StockEntry >= 0 Then
                .Value = .Value + StockEntry
                MsgBox strPrompt & "Stock Qty Updated", 64, "Success"
            Else
                MsgBox strPrompt & "Not Enough Stock", 48, "Error"
            End If
        End With
    Else
        MsgBox strPrompt & "Part Not Found", 48, "Not Found"
    End If
    
End Sub

Private Sub cmdClose_Click()
    Unload Me
End Sub

Private Sub txtPartID_AfterUpdate()
    With Me.txtPartID
        .Value = UCase(.Value)
    End With
End Sub

Private Sub txtQty_Change()
    Me.cmdUpdate.Enabled = IsNumeric(Me.txtQty)
End Sub

Private Sub UserForm_Initialize()
    Set wsPartsData = ThisWorkbook.Worksheets("PartsData")
    Me.cmdUpdate.Enabled = False
End Sub

You should ensure that the Controls are named as follows

TextBoxes
  • txtPartID
  • txtQty
CommandButtons
  • cmdUpdate
  • cmdClose
enter quantity as

- a positive number for parts added to stock

- a negative number for parts removed from stock

Soluion assumes that all Part Numbers in the table are unique



Dave
 
Upvote 0
dmt32 said:
Hi,

Based on what you are seeking to do, then see if this will help you

Place ALL codes as published in your userforms code page

Code: 
Dim wsPartsData     As Worksheet

Private Sub cmdUpdate_Click()
    Dim strPrompt   As String
    Dim m           As Variant, PartNo  As Variant
    Dim StockQty    As Long, StockEntry As Long
   
    PartNo = Me.txtPartID
    If IsNumeric(PartNo) Then PartNo = Val(PartNo)
   
    StockEntry = Val(Me.txtQty)
   
    strPrompt = "Part No: " & PartNo & Chr(10)
   
    m = Application.Match(PartNo, wsPartsData.Columns(1), 0)
    If Not IsError(m) Then
        With wsPartsData.Cells(CLng(m), 3)
            StockQty = .Value
            If StockQty + StockEntry >= 0 Then
                .Value = .Value + StockEntry
                MsgBox strPrompt & "Stock Qty Updated", 64, "Success"
            Else
                MsgBox strPrompt & "Not Enough Stock", 48, "Error"
            End If
        End With
    Else
        MsgBox strPrompt & "Part Not Found", 48, "Not Found"
    End If
   
End Sub

Private Sub cmdClose_Click()
    Unload Me
End Sub

Private Sub txtPartID_AfterUpdate()
    With Me.txtPartID
        .Value = UCase(.Value)
    End With
End Sub

Private Sub txtQty_Change()
    Me.cmdUpdate.Enabled = IsNumeric(Me.txtQty)
End Sub

Private Sub UserForm_Initialize()
    Set wsPartsData = ThisWorkbook.Worksheets("PartsData")
    Me.cmdUpdate.Enabled = False
End Sub

You should ensure that the Controls are named as follows

TextBoxes
  • txtPartID
  • txtQty
CommandButtons
  • cmdUpdate
  • cmdClose
enter quantity as

- a positive number for parts added to stock

- a negative number for parts removed from stock

Soluion assumes that all Part Numbers in the table are unique



Dave
Click to expand...
Hi Dave many thanks for your Help.
iv done exactly as you said.
If i add this code to the userform code the Update Stock button on the userform is "greyed out".
If i remove the code the Update Stock button is back to normal.
 

Attachments

  • With Code.jpg
    With Code.jpg
    212.4 KB · Views: 8
Upvote 0
dmt32 said:
Hi,

Based on what you are seeking to do, then see if this will help you

Place ALL codes as published in your userforms code page

Code: 
Dim wsPartsData     As Worksheet

Private Sub cmdUpdate_Click()
    Dim strPrompt   As String
    Dim m           As Variant, PartNo  As Variant
    Dim StockQty    As Long, StockEntry As Long
   
    PartNo = Me.txtPartID
    If IsNumeric(PartNo) Then PartNo = Val(PartNo)
   
    StockEntry = Val(Me.txtQty)
   
    strPrompt = "Part No: " & PartNo & Chr(10)
   
    m = Application.Match(PartNo, wsPartsData.Columns(1), 0)
    If Not IsError(m) Then
        With wsPartsData.Cells(CLng(m), 3)
            StockQty = .Value
            If StockQty + StockEntry >= 0 Then
                .Value = .Value + StockEntry
                MsgBox strPrompt & "Stock Qty Updated", 64, "Success"
            Else
                MsgBox strPrompt & "Not Enough Stock", 48, "Error"
            End If
        End With
    Else
        MsgBox strPrompt & "Part Not Found", 48, "Not Found"
    End If
   
End Sub

Private Sub cmdClose_Click()
    Unload Me
End Sub

Private Sub txtPartID_AfterUpdate()
    With Me.txtPartID
        .Value = UCase(.Value)
    End With
End Sub

Private Sub txtQty_Change()
    Me.cmdUpdate.Enabled = IsNumeric(Me.txtQty)
End Sub

Private Sub UserForm_Initialize()
    Set wsPartsData = ThisWorkbook.Worksheets("PartsData")
    Me.cmdUpdate.Enabled = False
End Sub

You should ensure that the Controls are named as follows

TextBoxes
  • txtPartID
  • txtQty
CommandButtons
  • cmdUpdate
  • cmdClose
enter quantity as

- a positive number for parts added to stock

- a negative number for parts removed from stock

Soluion assumes that all Part Numbers in the table are unique



Dave
Click to expand...
I think iv Sorted it now. Iv changed:

Private Sub UserForm_Initialize()
Set wsPartsData = ThisWorkbook.Worksheets("PartsData")
Me.cmdUpdate.Enabled = False
End Sub

To

Private Sub UserForm_Initialize()
Set wsPartsData = ThisWorkbook.Worksheets("PartsData")
Me.cmdUpdate.Enabled = True
End Sub

So many thanks for your help i never would of managed this without you input !!!!!
 
Upvote 0
Lynton said:
Hi Dave many thanks for your Help.
iv done exactly as you said.
If i add this code to the userform code the Update Stock button on the userform is "greyed out".
If i remove the code the Update Stock button is back to normal.
Click to expand...

That is quite intentional.
Leave the code as published - when you enter a Qty the Update button will be enabled

Dave
 
Upvote 0
Lynton said:
I think iv Sorted it now. Iv changed:
Me.cmdUpdate.Enabled = True
So many thanks for your help i never would of managed this without you input !!!!!
Click to expand...
The enabled state of the button is a control feature to ensure that you have entered a qty to submit and only then, will the button be enabled - suggest you return the code back to as it was published.

Glad solution helped & appreciate your feedback

Dave
 
Upvote 0
dmt32 said:
The enabled state of the button is a control feature to ensure that you have entered a qty to submit and only then, will the button be enabled - suggest you return the code back to as it was published.

Glad solution helped & appreciate your feedback

Dave
Click to expand...
Your correct iv altered it back to False and its exactly as you said. Thank you.
One last request could we make both Txt Boxes clear when the update button is pressed. it would speed up data input.
Many Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Record (Row) Locking in Excel
Replies
6
Views
311
Jeffrey Mahoney
Jeffrey Mahoney
N
  • Question Question
Excel VBA - issue with my VBA code
Replies
3
Views
345
dmt32
D
H
  • Question Question
Userform Help
Replies
5
Views
358
Hjemmet
H
W
  • Solved
vba/macro to Find a line on another sheet and replace information in just 1 cell in that line
Replies
5
Views
93
mumps
mumps
K
  • Solved
Code to search job id from multiple sources and update main spreadsheet.
2
Replies
10
Views
679
mumps
mumps

Forum statistics

Threads
1,224,811
Messages
6,181,081
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top