none
Help With Microsoft Products RRS feed

  • Question

  • Hi just after some suggestion for Microsoft product options.

    We currently have an excel spreadsheet that lists product options(with pictures too) for our building house selections.  I.e different styles of doors, driveways etc.

    At the moment we print this for clients and just mark with a pen what they have selected.  Is there something  I can create in excel, or another Microsoft products that we can click on the selection made and it add it to a selection area?  (i.e. like when you online shop & select your product and it adds it to your shopping cart).

    Appreciate any help or suggestions.

    Thanks

    Friday, October 7, 2016 4:51 AM

Answers

  • Hi RyBG,

    For local use you can try to develop something like this but it is not suitable for online use.

    As you had mentioned that you are printing the file and send it to customer so here I assume that you are going to use this locally.

    so on 1 sheet you can display your products something like below.

    you can see that I had placed an Activex Button on the sheet.

    when you click on the button the name and price of that product will be copy to be in the new sheet named "Shopping Cart".

    so when ever you click on the button the particular item will copy to shopping cart Sheet at the end of the last record.

    on the shopping cart sheet you can display the total Amount by doing the addition of price of all products.

    on the shopping cart sheet you can also place a button to "Remove the button from cart".

    and at the end you can place a button to "Place Order".

    when you click on place order it will mail the order to you so that you can know that some one has placed an order.

    The shopping cart sheet looks like below.

    you need to code using VBA.

    Following is the example of that.

    Following is the example to copy the data from 1 sheet to other.

    Sub demo()
    Sheets("Sheet1").Range("A1:B10").Copy Destination:=Sheets("Sheet2").Range("E1")
    
    End Sub

    to do the Addition you can use the code mentioned below.

    dExternalTotal = Application.WorksheetFunction.Sum(columns("A:A"))
    
    OR
    
    dExternalTotal = Application.WorksheetFunction.Sum(columns((rReportData.column))

    Following is the example code to send the worksheet in mail.

    Sub Mail_ActiveSheet()
    'Working in Excel 2000-2016
    'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
        Dim FileExtStr As String
        Dim FileFormatNum As Long
        Dim Sourcewb As Workbook
        Dim Destwb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim OutApp As Object
        Dim OutMail As Object
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        Set Sourcewb = ActiveWorkbook
    
        'Copy the ActiveSheet to a new workbook
        ActiveSheet.Copy
        Set Destwb = ActiveWorkbook
    
        'Determine the Excel version and file extension/format
        With Destwb
            If Val(Application.Version) < 12 Then
                'You use Excel 97-2003
                FileExtStr = ".xls": FileFormatNum = -4143
            Else
                'You use Excel 2007-2016
                Select Case Sourcewb.FileFormat
                Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    Else
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56: FileExtStr = ".xls": FileFormatNum = 56
                Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
                End Select
            End If
        End With
    
        '    'Change all cells in the worksheet to values if you want
        '    With Destwb.Sheets(1).UsedRange
        '        .Cells.Copy
        '        .Cells.PasteSpecial xlPasteValues
        '        .Cells(1).Select
        '    End With
        '    Application.CutCopyMode = False
    
        'Save the new workbook/Mail it/Delete it
        TempFilePath = Environ$("temp") & "\"
        TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        With Destwb
            .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
            On Error Resume Next
            With OutMail
                .to = "ron@debruin.nl"
                .CC = ""
                .BCC = ""
                .Subject = "This is the Subject line"
                .Body = "Hi there"
                .Attachments.Add Destwb.FullName
                'You can add other files also like this
                '.Attachments.Add ("C:\test.txt")
                .Send   'or use .Display
            End With
            On Error GoTo 0
            .Close savechanges:=False
        End With
    
        'Delete the file you have send
        Kill TempFilePath & TempFileName & FileExtStr
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    

    Reference:

    Mail from Excel with Outlook (Windows)

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 7, 2016 6:39 AM
    Moderator