How to add attachment button in vba excel

Question

I've developed a small ticketing system on excel VBA The Save button will take all data from textboxes and radio button and add it to the row number 7 (in this case) But when I press upload I can't add the link to the label of attachments

How to add link to the attachment label using (upload file ) button And also save the Link value stored in upload file button to use it later in clear button and save button?

I'm confused to work with private sub and private dim variables. I'm newbie in VBA please help

System of Ticketing

The Upload button

Public Sub btnAttachment_Click()

'To upload file link format is png, jpeg, PDF or All files'

Dim wks As Worksheet
Dim LinksList As Range
Dim lastRowLink As Long
Dim LinkAttached As Long

Set wks = ActiveSheet

Set LinksList = Range("N1")


'declare last row to insert link to
lastRowLink = WorksheetFunction.CountA(Sheets("Tickets").Range("A:A"))
 Sheets("Tickets").Cells(lastRow + 1, 11).Value = LinkAttached
 

    ChDrive "C:\"
    ChDir "C:\"
    Filt = "PNG Files(*.png),*.png ," & _
            "Jpeg Files(*.jpeg),*.jpg ," & _
            "PDF Files (*.pdf),*.pdf ," & _
            "All Files (*.*),*.*"
    FilterIndex = 1
    Title = "Select a File to Hyperlink"
    Filename = Application.GetOpenFilename _
        (FileFilter:=Filt, _
         FilterIndex:=FilterIndex, _
         Title:=Title)

    If Filename <> False Then
       wks.Hyperlinks.Add Anchor:=LinksList, _
       Address:=Filename, _
        TextToDisplay:=Filename
    Else
        MsgBox "No file was selected.", vbCritical, "Loading Error"
        Exit Sub
    End If
End Sub

The Save button code

Public Sub BtnSave_Click()

    Dim Ws As Worksheet
    Dim lastRow As Long
    Dim openOn As Date
   
    
    'Declare openBy to date Now function'
    openOn = Now()
    'set format function on time var'
    openTimeAmPM = Format(openOn, "m.d.yy h:mm AM/PM")
    'if no all information required inserted, show error'
    If SieraForum.txtTicketName.Value = "" Or (SieraForum.ErrorOption = False And SieraForum.OrderOption = False) Or SieraForum.CombLocation.Value = "" Or SieraForum.CombOpenBy.Value = "" Or SieraForum.CombTicketStatus.Value = "" Then
    MsgBox " Please fill All information required (Ticket Name, Severity, Location, OpenBy)"

    Else
    lastRow = WorksheetFunction.CountA(Sheets("Tickets").Range("A:A"))
    
    Sheets("Tickets").Cells(lastRow + 1, 1).Value = lastRow
    Sheets("Tickets").Cells(lastRow + 1, 2).Value = SieraForum.txtTicketName.Value
    
    If SieraForum.ErrorOption = True Then
     Sheets("Tickets").Cells(lastRow + 1, 3).Value = "Error"
    Else
     Sheets("Tickets").Cells(lastRow + 1, 3).Value = "Order"
    End If
       
    Sheets("Tickets").Cells(lastRow + 1, 4).Value = SieraForum.CombSeverity.Value
    Sheets("Tickets").Cells(lastRow + 1, 5).Value = SieraForum.CombLocation.Value
    Sheets("Tickets").Cells(lastRow + 1, 6).Value = SieraForum.txtTicketDetails.Value
    Sheets("Tickets").Cells(lastRow + 1, 7).Value = SieraForum.CombOpenBy.Value
    Sheets("Tickets").Cells(lastRow + 1, 8).Value = SieraForum.CombCloseBy.Value
    Sheets("Tickets").Cells(lastRow + 1, 9).Value = SieraForum.CombTicketStatus.Value
    Sheets("Tickets").Cells(lastRow + 1, 10).Value = openTimeAmPM
    
    'Sheets("Tickets").Cells(lastRow + 1, 13).Value =
    
    
    'Display new ticket number on the label'
    lbTicketNumVal.Caption = lastRow
   End If
  
  'to clear data after pressing Save button'
  
'Clear the data from the form'
SieraForum.txtTicketName.Value = ""
SieraForum.ErrorOption = False
SieraForum.OrderOption = False
SieraForum.CombSeverity = ""
SieraForum.CombLocation = ""
SieraForum.txtTicketDetails = ""
SieraForum.CombOpenBy = ""
SieraForum.CombCloseBy = ""
SieraForum.CombTicketStatus = ""
lbTicketNumVal.Caption = lastRow + 1
   
    
'to save excel sheet editing'
ActiveWorkbook.Save

     
End Sub

No, I have tickets for each time the user add a ticket it will submitted into a new row, the upload key should add the link of attached file in the same row ticket on cell M and each time will add the link into the same row of the ticket number — Abdulghani Alkhateeb
– Abdulghani Alkhateeb, Commented Aug 16, 2020 at 16:19
basically I need the same sub of Save button to be on Upload button in term of link value — Abdulghani Alkhateeb
– Abdulghani Alkhateeb, Commented Aug 16, 2020 at 16:21

Mike67 · Accepted Answer · 2020-08-16 16:55:56Z

0

If I'm understanding correctly, the ticker row will already exist in the sheet. For the upload, you just need to update the row with the link filename. There can also be multiple links.

At the bottom of btnAttachment_Click(), add this code:

rw = SieraForum.lbTicketNumVal.Caption   ' ticket number label
' append filename to cell value
Sheets("Tickets").Cells(rw, 13).Value = Sheets("Tickets").Cells(rw, 13).Value & vbcrlf & Filename

It assumes column 13 (M) is the link column you want to update. It gets the row number from the ticket label.

answered Aug 16, 2020 at 16:55

Mike67

11.3k2 gold badges9 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abdulghani Alkhateeb Over a year ago

No, the row not exist, it should open a new row,

Abdulghani Alkhateeb Over a year ago

Second thing , the link added as a string (long) I need it hyperlink, see the example below the variable LinksList it return the hyper link but I failed to write it into the same new row Dim wks As Worksheet Dim LinksList As Range Dim lastRowLink As Long Dim LinkAttached As Variant Set wks = ActiveSheet Set LinksList = Range("N1")

Collectives™ on Stack Overflow

How to add attachment button in vba excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related