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

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,832
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
 
Sort by date Sort by votes
Then this should be enough:
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 ANY value
          If Not IsEmpty(Worksheets("DATABASE").Range("P5")) Then
            'Display not empty message
            MsgBox "P5 is not empty."
          Else 'If P5 is empty
              '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

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi,
That didnt work.

I see the Msbox saying that P5 is not empty BUT & i click OK & it is now that no other code should run BUT it does.

Can we not do something right at the very begining ?

Is it empty yes then continue

If there is a value in the cell then just stop.

Rich (BB code): 
Sub HYPERLINKP5()
Dim answer As Integer
    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 ANY value
          If Not IsEmpty(Worksheets("DATABASE").Range("P5")) Then
            'Display not empty message
            MsgBox "P5 is not empty."
          Else 'If P5 is empty
              '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
    With Sheets("INV")
    Worksheets("INV").Activate
    Worksheets("INV").Range("G13").Select

    With ActiveSheet
         MsgBox "print disabled"
       
       '  ActiveWindow.SelectedSheets.PrintOut copies:=1


         answer = MsgBox("DID THE INVOICE PRINT OK ?", vbInformation + vbYesNo, "INVOICE PRINT OK MESSAGE")
    If answer = vbNo Then
    Exit Sub
    Else
      
    Range("L4").Value = Range("L4").Value + 1
    Range("G27:L36").ClearContents
    Range("G46:G50").ClearContents
    Range("L18").ClearContents
    Range("G13").ClearContents
    Range("G13").Select
    ActiveWorkbook.Save
    End If
    End With
    End With
   End Sub
 
Upvote 0
How about to exit function directly?
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 ANY value
          If Not IsEmpty(Worksheets("DATABASE").Range("P5")) Then
            'Display not empty message
            MsgBox "P5 is not empty."
            Exit Sub 'Exits function
          Else 'If P5 is empty
              '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
I was just typing.
I had seen that wasnt there so i added it.

Outcome now is i see the message P5 ISNT EMPTY but its after the value was put in cell P5

This is why i say add code right at the begining.
Currently the value is put in cell P5 Then the code is checking is P5 has a empty or not
 
Upvote 0
I am so so sorry.. I've just seen the line you copy the value... I totally missed that. I hope this will work for you:
VBA Code: 
Sub HYPERLINKP5()
    Dim srcWS As Worksheet, destWS As Worksheet
    Set srcWS = ActiveWorkbook.Worksheets("INV")
    Set destWS = ActiveWorkbook.Worksheets("DATABASE")
    If IsEmpty(destWS.Range("P5")) Then 'If P5 is empty do everything
      srcWS.Range("L4").Copy destWS.Range("P5")

      With Sheets("DATABASE")
      .Range("P5").Font.Size = 14
      .Activate
      .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
    Else 'If not empty
      MsgBox "P5 is not empty."
    End If
End Sub
 
Upvote 0
I still get an Compile error.

The error i see is CONSTANT EXPRESSION REQUIRED
In the code below the line shown in Red is what it debugs to in Yellow

Here is the code in use.
Rich (BB code): 
  Sub HYPERLINKP5()
    Dim srcWS As Worksheet, destWS As Worksheet
    Set srcWS = ActiveWorkbook.Worksheets("INV")
    Set destWS = ActiveWorkbook.Worksheets("DATABASE")
    If IsEmpty(destWS.Range("P5")) Then 'If P5 is empty do everything
      srcWS.Range("L4").Copy destWS.Range("P5")

      With Sheets("DATABASE")
      .Range("P5").Font.Size = 14
      .Activate
      .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
    Else 'If not empty
      MsgBox "P5 is not empty."
    End If
        With Sheets("INV")
        Worksheets("INV").Activate
        Worksheets("INV").Range("G13").Select

    With ActiveSheet
        MsgBox "PRINTING HAS BEEN DISABLED"
       
       '  ActiveWindow.SelectedSheets.PrintOut copies:=1


       answer = MsgBox("DID THE INVOICE PRINT OK ?", vbInformation + vbYesNo, "INVOICE PRINT OK MESSAGE")
    If answer = vbNo Then
    Exit Sub
    Else
      
       Range("L4").Value = Range("L4").Value + 1
       Range("G27:L36").ClearContents
       Range("G46:G50").ClearContents
       Range("L18").ClearContents
       Range("G13").ClearContents
       Range("G13").Select
       ActiveWorkbook.Save
    End If
    End With
    End With
   End Sub

End Sub
 
Upvote 0
Hi, I added some comments next to several lines. Other than that, I have no idea about that error sorry. Maybe you should start a new thread for this problem. I am sorry.
VBA Code: 
Sub HYPERLINKP5()
    Dim srcWS As Worksheet, destWS As Worksheet
    Set srcWS = ActiveWorkbook.Worksheets("INV")
    Set destWS = ActiveWorkbook.Worksheets("DATABASE")
    If IsEmpty(destWS.Range("P5")) Then 'If P5 is empty do everything
      srcWS.Range("L4").Copy destWS.Range("P5") 'Check if L4 really copies something to P5

      With destWS 'I used the variable name for the worksheet which we know that it works.
      .Range("P5").Font.Size = 14
      .Activate
      .Range("P5").Select
 
      Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" 'Check if really such directory exists
      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
    Else 'If not empty
      MsgBox "P5 is not empty."
    End If
        With Sheets("INV")
        Worksheets("INV").Activate
        Worksheets("INV").Range("G13").Select

    With ActiveSheet
        MsgBox "PRINTING HAS BEEN DISABLED"
     
       '  ActiveWindow.SelectedSheets.PrintOut copies:=1


       answer = MsgBox("DID THE INVOICE PRINT OK ?", vbInformation + vbYesNo, "INVOICE PRINT OK MESSAGE")
    If answer = vbNo Then
    Exit Sub
    Else
    
       Range("L4").Value = Range("L4").Value + 1
       Range("G27:L36").ClearContents
       Range("G46:G50").ClearContents
       Range("L18").ClearContents
       Range("G13").ClearContents
       Range("G13").Select
       ActiveWorkbook.Save
    End If
    End With
    End With
   End Sub
 
Upvote 0
Oh, I think I know the reason. You didn't make a logical statement. Length of directory is compared to what?
Either try:
If Not Len(Dir(FILE_PATH & ActiveCell.Value & ".pdf")) = 0 Then 'If this doesn't work, try to delete NOT
Or
If Dir(FILE_PATH & ActiveCell.Value & ".pdf") <> "" Then
 
Last edited by a moderator:
Upvote 0
I will just leave this & hopefully wait for other advice as im getting deeper & deeper in a mess.
The code stil runs everything
 
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
124
ipbr21054
I
I
  • Question Question
Advice for slight alteration to existing working code
Replies
1
Views
236
ipbr21054
I
I
  • Question Question
Look for value in column A then check if cell has a value
Replies
4
Views
522
ipbr21054
I
I
  • Question Question
Can i apply a hyperlink without opening the other worksheet
Replies
1
Views
246
james_martin001
J
I
  • Question Question
Unload Me still runs following code
Replies
9
Views
322
RoryA
RoryA

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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