+ Reply to Thread
Results 1 to 9 of 9

(updating inventory using user form) : Assign macro to "Çheck Availibility" command button

Hybrid View

pyol17 (updating inventory using... 12-07-2012, 02:27 PM
protonLeah Re: (updating inventory using... 12-07-2012, 04:55 PM
pyol17 Re: (updating inventory using... 12-07-2012, 08:15 PM
Leith Ross Re: (updating inventory using... 12-07-2012, 11:14 PM
pyol17 Re: (updating inventory using... 12-07-2012, 11:30 PM
jindon Re: (updating inventory using... 12-08-2012, 01:23 AM
pyol17 Re: (updating inventory using... 12-08-2012, 02:00 AM
jindon Re: (updating inventory using... 12-08-2012, 02:50 AM
pyol17 Re: (updating inventory using... 12-08-2012, 06:50 AM
  1. #1
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    (updating inventory using user form) : Assign macro to "Çheck Availibility" command button

    Hi guys.

    I need some help here. I want a macro that can update the inventory by using the user form. I need a macro to be assigned on "check availability" command button.

    Every time the user insert the item code in textbox1, the macro will identify the value in textbox1 which match the code in database column C.Then, it will display the respective quantity in column I into textbox2 (user form).

    However, if the textbox1 (user form) = "" or value in textbox1(userform) doesnt not exist in database, the macro need to display "Not available" message.


    Here are the the code that i try to modify, but it doesnt fully work on certain condition

    Private Sub CommandButton1_Click()
    x = 9
    
    Dim qty As Integer
    qty = 0
    Do While Cells(x, 3) <> ""
    If TextBox1.Text = Cells(x, 3) Then
    TextBox2.Value = Cells(x, 9)
    qty = qty + TextBox2.Value
    End If
    x = x + 1
    Loop
    TextBox2.Value = qty
    MsgBox "WRONG"
    End Sub
    I hope you can help me on this. Thanks in advance. Really appreciate your help.
    Attached Files Attached Files
    Last edited by pyol17; 12-07-2012 at 08:13 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    Win10/MSO2016
    Posts
    13,019

    Re: (updating inventory using user form) : Assign macro to "Çheck Availibility" command bu

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code (not [quote][/quote ] tags). Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: (updating inventory using user form) : Assign macro to "Çheck Availibility" command bu

    dear proton leah,
    thanks for reminder
    done edited.

    aelgadi,
    yeah. i will if you solve this. any idea how?

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: (updating inventory using user form) : Assign macro to "Çheck Availibility" command bu

    Hello pyol17,

    You don't need the "Check" button. I have changed the form for you to check the availability without using the button. The updated workbook is attached.
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  5. #5
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: (updating inventory using user form) : Assign macro to "Çheck Availibility" command bu

    Yeah..it looks great. Thanks.

    Actually i also want to ask for the code to update the inventory. However it is considered under different question and i will start it in a new thread.


    For my previous question, i already got what i need. Thread solved. Thanks again

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: (updating inventory using user form) : Assign macro to "Çheck Availibility" command bu

    Quote Originally Posted by pyol17 View Post

    Actually i also want to ask for the code to update the inventory.
    Different method including "Update functionality"
    Option Explicit
    
    Private dic As Object
    
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        With Me.TextBox1
            Me.Label4.Caption = ""
            If Len(.Value) Then
                If dic.exists(.Value) Then
                    Me.Label4.Caption = dic(.Value).Value
                    Me.CommandButton2.Enabled = True
                Else
                    Me.Label4.Caption = "Not available"
                End If
            End If
            Me.CommandButton2.Enabled = Len(.Value)
        End With
    End Sub
    
    Private Sub UserForm_Initialize()
        Dim i As Long
        Set dic = CreateObject("Scripting.Dictionary")
        dic.CompareMode = 1
        With Sheets("sheet1").Range("c8").CurrentRegion
            For i = 2 To .Rows.Count
                Set dic(.Cells(i, 1).Value) = .Cells(i, 7)
            Next
        End With
        Me.CommandButton2.Enabled = False
    End Sub
    
    Private Sub CommandButton2_Click()
        Dim temp As Double
        With Me
            If dic.exists(.TextBox1.Value) Then
                If (Me.OptionButton1) + (Me.OptionButton2) Then
                    temp = dic(Me.TextBox1.Value).Value _
                    + (Val(.TextBox3.Value) * IIf(.OptionButton1, 1, -1))
                    If temp < 0 Then
                        MsgBox "Short in stock by " & Abs(temp), , .TextBox1.Value
                    Else
                        dic(.TextBox1.Value).Value = temp
                        .Label4.Caption = temp
                    End If
                End If
            Else
                MsgBox .TextBox1.Value & " is not available"
            End If
        End With
    End Sub
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: (updating inventory using user form) : Assign macro to "Çheck Availibility" command bu

    wow..Thats very quick. Thanks JIndon. Long time no see huh..

    Can you do me one favor. I prefer every time i click the "update inventory" button, the system ask me the "Are you sure to proceed this transaction?"or something like that. You know,let say if someone mistakenly commit some error.

    Thanks Jindon. You're very great on this...

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: (updating inventory using user form) : Assign macro to "Çheck Availibility" command bu

    Replace "CommandButton2_Click" with
    Private Sub CommandButton2_Click()
        Dim temp As Double
        With Me
            If dic.exists(.TextBox1.Value) Then
                If (Me.OptionButton1) + (Me.OptionButton2) Then
                    temp = dic(Me.TextBox1.Value).Value _
                    + (Val(.TextBox3.Value) * IIf(.OptionButton1, 1, -1))
                    If temp < 0 Then
                        MsgBox "Short in stock by " & Abs(temp), , .TextBox1.Value
                    Else
                        If vbYes = MsgBox("Update stock : " & .TextBox1.Value & _
                            vbLf & "Now : " & vbTab & .Label4.Caption & vbLf & _
                            "After update : " & temp, vbYesNo + vbQuestion, "Comfirm") Then
                            dic(.TextBox1.Value).Value = temp
                            .Label4.Caption = temp
                        End If
                    End If
                End If
            Else
                MsgBox .TextBox1.Value & " is not available"
            End If
        End With
    End Sub

  9. #9
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: (updating inventory using user form) : Assign macro to "Çheck Availibility" command bu

    thanks bro. Appreciate your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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