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

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,699
Office Version
  1. 2007
Platform
  1. Windows
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
Rich (BB code): 
    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

Rich (BB code): 
    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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Like this maybe? I quite didn't get the order of your procedures and what you mean by pasting.
VBA Code: 
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
 
Upvote 0
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
 
Upvote 0
I have pasted the code but get an error message

INVALID OUTSIDE PROCEDURE

This line is in yellow

Rich (BB code): 
Set srcWS = ActiveWorkbook.Worksheets("INV")
 
Upvote 0
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


Rich (BB code): 
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
 
Upvote 0
Like this:
VBA Code: 
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
 
Upvote 0
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.
 
Upvote 0
VBA Code: 
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Add date to worksheet when userform is closed
Replies
5
Views
109
ipbr21054
I
I
  • Question Question
Advice for slight alteration to existing working code
Replies
1
Views
192
ipbr21054
I
I
  • Question Question
Look for value in column A then check if cell has a value
Replies
4
Views
509
ipbr21054
I
I
  • Question Question
Can i apply a hyperlink without opening the other worksheet
Replies
1
Views
208
james_martin001
J
I
  • Question Question
Unload Me still runs following code
Replies
9
Views
296
RoryA
RoryA

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top