VBA Newbie Need Help Please!

[Seekr]

Hello everyone, I am pretty new to working with VBA with Excel. I've worked with a bunch of different coding languages but all pretty entry level. Anyways, I have been trying to make an inventory sheet for my place of employment (public library) that can do what we need. I have worked my way though most of it. Got it to send an email based on a cell, made a form for our employees to use, and set up our basic design. I am just stuck on a couple things.

1. I am using a combo box in my form for the items in our inventory. What I want to do is when we select an item in the list and hit submit I just want it to subtract one from a specific cell. Sounds simple I just don't know where to start.

2. The email macro is working quite well but, the IF statement I had working now just sends the email regardless when I had it sending only if there was something below its limit. I am not sure how to include code on here if anyone can help me out that would be great.

3. The last part is also with the email. I want it to send only the cells that are "low in stock" and what the stock is for those cells.

Thanks so much everyone hope to hear from you soon!

 

[Seekr]

Sorry read the Tips for posting.....
Changed the emails and password for obvious reasons.
G2:G100 is empty right now and it is still sending the email.

Private Sub Workbook_Open()
   Order = Range("G2:G100")
   If IsEmpty(Order) = True Then Exit Sub
   
Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String


strSubject = "Inventory Order Sheet"
strFrom = "from"
strTo = "to"
strCc = ""
strBcc = ""
strBody = "We need the following supplies: " & vbNewLine & _
CStr(Sheet1.Cells(2, 7)) & " - We Currently Have " & CStr(Sheet1.Cells(2, 2)) & " Left In Stock."


Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling


Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1


Set SMTP_Config = CDO_Config.Fields


With SMTP_Config
 .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username"
 .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Update
End With


With CDO_Mail
 Set .Configuration = CDO_Config
End With


CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send


Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description


End Sub

 

[DanteAmor]

1. I am using a combo box in my form for the items in our inventory. What I want to do is when we select an item in the list and hit submit I just want it to subtract one from a specific cell. Sounds simple I just don't know where to start.

How do you load the data in the combo?
And what do you want to subtract 1?
To which cell?

2. The email macro is working quite well but, the IF statement I had working now just sends the email regardless when I had it sending only if there was something below its limit.

What is the limit and in which cell is it reviewed?

3. The last part is also with the email. I want it to send only the cells that are "low in stock" and what the stock is for those cells.

How much is below the stock and in which cell is it checked?

[COLOR=#333333]Order = Range("G2:G100")
[/COLOR][COLOR=#333333]   If IsEmpty(Order) = True Then Exit Sub[/COLOR]

That way you can not check if the range is empty. Better explain what you need to review.

 

[Seekr]

How do you load the data in the combo?
And what do you want to subtract 1?
To which cell?

I want to subtract 1 from from the corresponding cell of the item they choose from the drop down box. Ex. Tissues is in A1 and Stock is in A2 If they select tissues and hit submit it subtracts 1 from A2.

What is the limit and in which cell is it reviewed?

The limit is going to be different for each item and in reference to the example above it will be in A3.

How much is below the stock and in which cell is it checked?

Any time A2 is below A3 is when I would want an email sent out. I have an equation already in G2 doing this for me but, I am not at work. Basically a simple if A2>A3 then statement in G2.

 

[DanteAmor]

Mmmm?
You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
Explain on a sheet what you need, use the data you are sending.

 

[Seekr]

Alright I put notes in and I believe all the "sensitive" information is out. I also believe the link should work. Here it is. https://www.dropbox.com/s/5nqdgrigup80nle/Inventory.xlsm?dl=0

 

[DanteAmor]

To subtract 1

Private Sub Submit_Click()
    Dim lRow As Long
    Dim lPart As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    
    'find first empty row in database
    'lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
    lPart = Me.BarcodeBox.ListIndex
    
    'check for a part number
    If Trim(Me.BarcodeBox.Value) = "" Or [COLOR=#0000ff]Me.BarcodeBox.ListIndex = -1[/COLOR] Then
        Me.BarcodeBox.SetFocus
        MsgBox "Please Select a Barcode"
        Exit Sub
    End If
[COLOR=#0000ff]    Set b = ws.Range("Barcode").Find(Me.BarcodeBox.Value, LookIn:=xlValues, lookat:=xlWhole)[/COLOR]
[COLOR=#0000ff]    If Not b Is Nothing Then[/COLOR]
[COLOR=#0000ff]        b.Offset(0, 1).Value = b.Offset(0, 1).Value - 1[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
    
    'clear the data
    Me.BarcodeBox.Value = ""
    Me.BarcodeBox.SetFocus


End Sub

 

[Seekr]

That worked perfectly! Thanks! I had seen that code when I was looking to find a way to do it but I had no idea what the offset was doing so I didn't want to mess with it. Any idea on the email/sheet1 stuff? Again thanks so much.

 

[DanteAmor]

Try this. Put the following code in your userform:

Private Sub Cancel_Click()
    Unload Me
End Sub


Private Sub Submit_Click()
    Dim lRow As Long
    Dim lPart As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    
    'find first empty row in database
    'lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
    lPart = Me.BarcodeBox.ListIndex
    
    'check for a part number
    If Trim(Me.BarcodeBox.Value) = "" Or Me.BarcodeBox.ListIndex = -1 Then
        Me.BarcodeBox.SetFocus
        MsgBox "Please Select a Barcode"
        Exit Sub
    End If
    Set b = ws.Range("Barcode").Find(Me.BarcodeBox.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not b Is Nothing Then
        b.Offset(0, 1).Value = b.Offset(0, 1).Value - 1
[COLOR=#0000ff]        If b.Offset(0, 1).Value < b.Offset(0, 2).Value Then[/COLOR]
[COLOR=#0000ff]            Call Send_Mail(b)[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]
    End If
    
    'clear the data
    Me.BarcodeBox.Value = ""
    Me.BarcodeBox.SetFocus


End Sub


Sub Send_Mail(b)
    Dim CDO_Mail As Object
    Dim CDO_Config As Object
    Dim SMTP_Config As Variant
    Dim strSubject As String
    Dim strFrom As String
    Dim strTo As String
    Dim strCc As String
    Dim strBcc As String
    Dim strBody As String
    
    strSubject = "Inventory Order Sheet"
    strFrom = "email1"
    strTo = "email2"
    strCc = ""
    strBcc = ""
    strBody = "We need the following supplies: " & vbNewLine & _
        b.Value & " - We Currently Have " & b.Offset(0, 1).Value & " Left In Stock."
    
    Set CDO_Mail = CreateObject("CDO.Message")
    On Error GoTo Error_Handling
    
    Set CDO_Config = CreateObject("CDO.Configuration")
    CDO_Config.Load -1
    
    Set SMTP_Config = CDO_Config.Fields
    
    With SMTP_Config
     .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
     .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
     .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
     .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username"
     .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "pass"
     .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
     .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
     .Update
    End With
    
    With CDO_Mail
     Set .Configuration = CDO_Config
    End With
    
    CDO_Mail.Subject = strSubject
    CDO_Mail.From = strFrom
    CDO_Mail.To = strTo
    CDO_Mail.TextBody = strBody
    CDO_Mail.CC = strCc
    CDO_Mail.BCC = strBcc
    CDO_Mail.Send
    
Error_Handling:
    If Err.Description <> "" Then MsgBox Err.Description
End Sub

 

[Seekr]

Amazing! I think it is doing everything I want it to do now. Thank you so very much! I didn't realize it could all be done right there in the userform! Many thanks!