Check if there is a value in the cell before pasting value

[ipbr21054]

Morning,

I am using the code shown below but getting confused trying to get it to work with some new added code.

I am in need of checking if a value is present in cell P5
If the cell is empty continue to paste value but if the cell has a present value then show MsgBox

ORIGINAL CODE WITHOUT ANY CHEKS

    Sub HYPERLINKP5()
    Dim srcWS As Worksheet, destWS As Worksheet
    Set srcWS = ActiveWorkbook.Worksheets("INV")
    Set destWS = ActiveWorkbook.Worksheets("DATABASE")
    srcWS.Range("L4").Copy destWS.Range("P5")
    
    With Sheets("DATABASE")
    .Range("P5").Font.Size = 14
    End With
    
    With Sheets("DATABASE")
    Worksheets("DATABASE").Activate
    Worksheets("DATABASE").Range("P5").Select
    
    Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\"
    If ActiveCell.Column = Columns("P").Column Then
        If Len(Dir(FILE_PATH & ActiveCell.Value & ".pdf")) Then
            ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".pdf"
            
        Else
            ActiveCell.Hyperlinks.Delete
            MsgBox (FILE_PATH & ActiveCell.Value & ".pdf" & vbNewLine & vbNewLine & "FILE IS NOT IN FOLDER SPECIFIED, PLEASE CHECK PATH IS CORRECT"), vbCritical
        End If
    Else
        MsgBox "PLEASE SELECT AN INVOICE NUMBER.", vbExclamation, "HYPERLINKING THE INVOICE NUMBER"
    End If
    End With
End Sub

ADDING THE CHECK CODE BUT FAILING WITH THE BASIC PART IN RED

I would like to check if cell P5 is empty, If the cell is empty then continue to paste new value in cell P5, as shown using blue code.
If cell P5 has a value in it Then i need to see a MsgBox advising the user, Basically dont paste the new value & to allow the user to go check it out first

    Sub HYPERLINKP5()
    Dim srcWS As Worksheet, destWS As Worksheet
    Set srcWS = ActiveWorkbook.Worksheets("INV")
    Set destWS = ActiveWorkbook.Worksheets("DATABASE")
    srcWS.Range("L4").Copy destWS.Range("P5")
    
    With Sheets("DATABASE")
    .Range("P5").Font.Size = 14
    End With
    
    With Sheets("DATABASE")
    Worksheets("DATABASE").Activate
    Worksheets("DATABASE").Range("P5").Select
    
    
    
    If Range("P5").Value = "" Then
    Continue with pasting new value
    
    Otherwise
    MsgBox "Value in cell test message"
        
    
    
    Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\"
    If ActiveCell.Column = Columns("P").Column Then
        If Len(Dir(FILE_PATH & ActiveCell.Value & ".pdf")) Then
            ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".pdf"
            
        Else
            ActiveCell.Hyperlinks.Delete
            MsgBox (FILE_PATH & ActiveCell.Value & ".pdf" & vbNewLine & vbNewLine & "FILE IS NOT IN FOLDER SPECIFIED, PLEASE CHECK PATH IS CORRECT"), vbCritical
        End If
    Else
        MsgBox "PLEASE SELECT AN INVOICE NUMBER.", vbExclamation, "HYPERLINKING THE INVOICE NUMBER"
    End If
    End With
End Sub
End Sub

 

[Legacy 143009]

Like this maybe? I quite didn't get the order of your procedures and what you mean by pasting.

Sub HYPERLINKP5()
    Dim srcWS As Worksheet, destWS As Worksheet
    Set srcWS = ActiveWorkbook.Worksheets("INV")
    Set destWS = ActiveWorkbook.Worksheets("DATABASE")
    srcWS.Range("L4").Copy destWS.Range("P5")
 
    With Sheets("DATABASE")
    .Range("P5").Font.Size = 14
    End With
 
    With Sheets("DATABASE")
    Worksheets("DATABASE").Activate
    Worksheets("DATABASE").Range("P5").Select
 
    Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\"
    If ActiveCell.Column = Columns("P").Column Then
        If Len(Dir(FILE_PATH & ActiveCell.Value & ".pdf")) Then
          If IsEmpty(Worksheets("DATABASE").Range("P5")) Then
            ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".pdf"
          Else
             MsgBox "P5 is not empty."
          End If
        Else
            ActiveCell.Hyperlinks.Delete
           MsgBox (FILE_PATH & ActiveCell.Value & ".pdf" & vbNewLine & vbNewLine & "FILE IS NOT IN FOLDER SPECIFIED, PLEASE CHECK PATH IS CORRECT"), vbCritical
        End If
    Else
        MsgBox "PLEASE SELECT AN INVOICE NUMBER.", vbExclamation, "HYPERLINKING THE INVOICE NUMBER"
    End If
    End With
End Sub

 

[ipbr21054]

Hi,

On one sheet a value is copied.
Then i need it to be put in a cell on another sheet & in the same workbook BUT do not wish to overwrite anything if the cell has a value.
Hence why im checking first.

Thannks

 

[Legacy 143009]

Have you tried the code? Does it do what you want?

 

[ipbr21054]

I have pasted the code but get an error message

INVALID OUTSIDE PROCEDURE

This line is in yellow

Set srcWS = ActiveWorkbook.Worksheets("INV")

 

[ipbr21054]

I have changed the path so i can test this but having an issue with the code supplied above.
The code in use is shown below & currently runs like so.

A value is placed in cell P5 but the code then checks if a value is present.
As there is a value present i then see the Message "P5 is not empty"

THE ABOVE RUNS IN THE OPPOSITE OF WHAT I REQUIRE

It should run like so,
Check if value is in cell P5 first.
If cell P5 has a value in it then show message "P5 is not empty"
BUT
If cell P5 is empty then continue to add the value to the cell

Sub HYPERLINKP5()
    Dim srcWS As Worksheet, destWS As Worksheet
    Set srcWS = ActiveWorkbook.Worksheets("INV")
    Set destWS = ActiveWorkbook.Worksheets("DATABASE")
    srcWS.Range("L4").Copy destWS.Range("P5")
 
    With Sheets("DATABASE")
    .Range("P5").Font.Size = 14
    End With
 
    With Sheets("DATABASE")
    Worksheets("DATABASE").Activate
    Worksheets("DATABASE").Range("P5").Select
 
    Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES TEST\"
    If ActiveCell.Column = Columns("P").Column Then
        If Len(Dir(FILE_PATH & ActiveCell.Value & ".pdf")) Then
          If IsEmpty(Worksheets("DATABASE").Range("P5")) Then
            ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".pdf"
          Else
             MsgBox "P5 is not empty."
          End If
        Else
            ActiveCell.Hyperlinks.Delete
           MsgBox (FILE_PATH & ActiveCell.Value & ".pdf" & vbNewLine & vbNewLine & "FILE IS NOT IN FOLDER SPECIFIED, PLEASE CHECK PATH IS CORRECT"), vbCritical
        End If
    Else
        MsgBox "PLEASE SELECT AN INVOICE NUMBER.", vbExclamation, "HYPERLINKING THE INVOICE NUMBER"
    End If
    End With
End Sub

 

[Legacy 143009]

Like this:

Sub HYPERLINKP5()
    Dim srcWS As Worksheet, destWS As Worksheet
    Set srcWS = ActiveWorkbook.Worksheets("INV")
    Set destWS = ActiveWorkbook.Worksheets("DATABASE")
    srcWS.Range("L4").Copy destWS.Range("P5")
 
    With Sheets("DATABASE")
    .Range("P5").Font.Size = 14
    End With
 
    With Sheets("DATABASE")
    Worksheets("DATABASE").Activate
    Worksheets("DATABASE").Range("P5").Select
 
    Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES TEST\"
    If ActiveCell.Column = Columns("P").Column Then
        If Len(Dir(FILE_PATH & ActiveCell.Value & ".pdf")) Then
          If Worksheets("DATABASE").Range("P5").Value = "Certain Value" Then
            MsgBox "P5 is not empty."
          Else
            If IsEmpty(Worksheets("DATABASE").Range("P5")) Then
              ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".pdf"
            End If
          End If
        Else
            ActiveCell.Hyperlinks.Delete
           MsgBox (FILE_PATH & ActiveCell.Value & ".pdf" & vbNewLine & vbNewLine & "FILE IS NOT IN FOLDER SPECIFIED, PLEASE CHECK PATH IS CORRECT"), vbCritical
        End If
    Else
        MsgBox "PLEASE SELECT AN INVOICE NUMBER.", vbExclamation, "HYPERLINKING THE INVOICE NUMBER"
    End If
    End With
End Sub

 

[ipbr21054]

Pasting is when the value is entered in cell P5

I will check this out when home.

In the mean time can you repost this code and highlite what code you added so I can follow.

 

[Legacy 143009]

Sub HYPERLINKP5()
    Dim srcWS As Worksheet, destWS As Worksheet
    Set srcWS = ActiveWorkbook.Worksheets("INV")
    Set destWS = ActiveWorkbook.Worksheets("DATABASE")
    srcWS.Range("L4").Copy destWS.Range("P5")
 
    With Sheets("DATABASE")
    .Range("P5").Font.Size = 14
    End With
 
    With Sheets("DATABASE")
    Worksheets("DATABASE").Activate
    Worksheets("DATABASE").Range("P5").Select
 
    Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES TEST\"
    If ActiveCell.Column = Columns("P").Column Then
        If Len(Dir(FILE_PATH & ActiveCell.Value & ".pdf")) Then
          'Check if P5 has certain value
          If Worksheets("DATABASE").Range("P5").Value = "Certain Value" Then
            'Display not empty message
            MsgBox "P5 is not empty."
          'If P5 is empty
          ElseIf IsEmpty(Worksheets("DATABASE").Range("P5")) Then
              'Add hyperlink to active cell
              ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".pdf"
          End If
        Else
            ActiveCell.Hyperlinks.Delete
           MsgBox (FILE_PATH & ActiveCell.Value & ".pdf" & vbNewLine & vbNewLine & "FILE IS NOT IN FOLDER SPECIFIED, PLEASE CHECK PATH IS CORRECT"), vbCritical
        End If
    Else
        MsgBox "PLEASE SELECT AN INVOICE NUMBER.", vbExclamation, "HYPERLINKING THE INVOICE NUMBER"
    End If
    End With
End Sub

 

[ipbr21054]

With respect where you write certain value.

I’m interested if ANY value is present in cell P5

Reason being is If a value is present the user needs to check it up HENCE do not run any other code & show user a Msgbox so he can check.

If cell P5 is empty / no value present then just continue with the code.

So does this make a difference to what you have advised ?

Should or is it best to check if cell P5 has a value present right at the very beginning.

Then either show user Msgbox or continue to run code.