Yes this worked! Thank you so much! I see where I went wrong now. I appreciate the help very much.
Posts by iamwickid
-
-
Target should be if one cell in the specified target column is "Yes" then carry out the code.
I tried adding If Intersect(Target, Range("U:U, X:X"")) Is Nothing Then Exit Sub. "U" being for target column 21 and "X" being for target column 24 but that didn't help. Is this what you were referring to above?
Thanks for the help it is much appreciated. -
Thanks for your response. However moving the delete entire row code for target.column=24 creates the same runtime error now with the debug column "If Target.column = 24 Then" highlighted rather than 21 as in my previous post.
-
Any idea why i am getting a run time error with the code shown below. I f i hit debug the colum "If Target.column = 21 Then" is highlighted. Basically I am just trying to delete a row if Yes is in column 24 and if yes is in column 21 then i want the row moved to another spreadsheet. Everything works and the code does what i want, i just get this pesky run time error that pops up anyway. Thanks very much!
***********************************************************************************************************************************
Code
Display MoreIf Target.Column = 24 Then If Target = "Yes" Then YesNo = MsgBox("Do you want to delete the entire contents of this row?", vbYesNo + vbCritical, "Caution") Select Case YesNo Case vbYes For Each r In Target If UCase(r.Value) = "YES" Then Application.EnableEvents = False Sheet1.Unprotect ("gf") Sheet6.Unprotect ("gf") Target.EntireRow.Delete End If Next r Application.EnableEvents = True Sheet1.Protect ("gf"), AllowFiltering:=True, AllowFormattingColumns:=True, AllowDeletingRows:=True Sheet6.Protect ("gf"), AllowFiltering:=True, AllowFormattingColumns:=True, AllowDeletingRows:=True Case vbNo If Target = "Yes" Then Target.ClearContents End If End Select End If End If If Target.Column = 21 Then If Target = "Yes" Then YesNo = MsgBox("Do you want to move this transaction to the Error Log?", vbYesNo + vbCritical, "Caution") Select Case YesNo Case vbYes For Each r In Target If UCase(r.Value) = "YES" Then Application.EnableEvents = False Sheet1.Unprotect ("gf") Sheet6.Unprotect ("gf") nxtRow = Sheets("Error Log").Range("A" & Rows.Count).End(xlUp).Row + 1 Target.EntireRow.Copy Destination:=Sheets("Error Log").Range("A" & nxtRow) Target.EntireRow.Delete End If Next r Application.EnableEvents = True Sheet1.Protect ("gf"), AllowFiltering:=True, AllowFormattingColumns:=True, AllowDeletingRows:=True Sheet6.Protect ("gf"), AllowFiltering:=True, AllowFormattingColumns:=True, AllowDeletingRows:=True Case vbNo If Target = "Yes" Then Target.ClearContents End If End Select End If End If End Sub
-
I got it to work however I encounter so problems. If something is entered into the active cell and enter is pressed to accept the entry the hyperlink then appears in the next active cell (being the cell below). Also if values are copied and pasted into Column A I had hoped they would all be assigned the appropriate hyperlink however this only triggers the hyperlink to appear in the active cell after pasting values.
Solutions?Thanks!
-
For some reason that line above forces excel to close as soon as I enter something in column 1 !
-
This is the recorded macro for one document entry. I require it to apply to any document number that gets entered in the cells in range (column A). Thanks!
Code
Display MoreSub Hyperlink() ' ' Hyperlink Macro ' ' ActiveCell.FormulaR1C1 = "2550008" Range("A3").Select Selection.Copy Sheets("Tracking Sheet").Select Application.CutCopyMode = False ActiveSheet.Unprotect ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _ "pcdocs://docs/2550008/R" Sheets("Tracking Sheet").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub
-
I was wondering how I would be able to create a hyperlink based on the cell value which is entered.
The link looks like this : pcdocs://docs/2036625/R
If someone enters a value into any cell in column A (the value being the "document number") it would create the hyperlink based on the document number entered and would add the hyperlink to the entered text in the cell. The document number is bolded in the link above.
Thanks!
-
So the code you provided above works great for the copy function. How do I now embed this code which states if another target (column 20) is yes then it moves the entire row to another spreadsheet and deletes the row. the code below works on its own, but once I add the other one you provided for copying to "Error Log" it doesn't work.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 20 Then
If Target = "Yes" Then
Application.EnableEvents = False
Sheet1.Unprotect ("fg")
Sheet6.Unprotect ("fg")
nxtRow = Sheets("PA LOG").Range("A" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy _
Destination:=Sheets("PA LOG").Range("A" & nxtRow)
Target.EntireRow.Delete
End If
End If
Application.EnableEvents = True
Sheet1.Protect ("fg")
Sheet6.Protect ("fg")
End Sub -
Copy A, E, F to A,B,C of Error Log! Thanks!
-
I am looking for a way to modify this current VBA code I have working. What it is currently doing is once anything in column 7 is "yes" it will copy the entire row to the next available row of another sheet titled "Error Log".
However what I want it to do is only copy "cell A, E and F" from the same row that the "yes" has been met. My current code is below. Help is greatly appreciated!Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
If Target = "Yes" Then
Application.EnableEvents = False
nxtRow = Sheets("Error Log").Range("A" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy _
Destination:=Sheets("Error Log").Range("A" & nxtRow)
End If
End If
Application.EnableEvents = True
End Sub