Lil Stinker
Board Regular
- Joined
- Feb 16, 2022
- Messages
- 151
- Office Version
- 2019
- Platform
- Windows
I'm using the following code to add, copy, edit client info from my client table stored on Sheet3 to my main form on Sheet1. It works great for these purposes. However, now I want to implement the same functionality for another worksheet with different range results while still pulling from the same client table worksheet.
I've tried using the same code and changing the ranges accordingly but it doesn't run correctly on Sheet2. No errors come up. It just doesn't recognize the client and wants me to add them again. How would I go about achieving the same result I get from Sheet1 on Sheet2?
Apologies if the code is inefficient, I'm new to VBA. Appreciate any help you can provide.
I've tried using the same code and changing the ranges accordingly but it doesn't run correctly on Sheet2. No errors come up. It just doesn't recognize the client and wants me to add them again. How would I go about achieving the same result I get from Sheet1 on Sheet2?
Apologies if the code is inefficient, I'm new to VBA. Appreciate any help you can provide.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C13")) Is Nothing And Range("C13").Value <> Empty Then
Dim projROW As Long
If Range("N2").Value <> Empty Then 'project exists
Sheet4.Range("M1").Value = "EDIT"
ActiveSheet.Shapes("EDITPRObttn").Visible = msoCTrue
projROW = Range("N2").Value 'project id
Range("B5").Value = Sheet3.Range("D" & projROW).Value 'billing address 1
Range("B6").Value = Sheet3.Range("E" & projROW).Value 'billing address 2
Range("B7").Value = Sheet3.Range("F" & projROW).Value 'billing address 3
Range("B8").Value = Sheet3.Range("G" & projROW).Value 'billing address 4
Range("B9").Value = Sheet3.Range("H" & projROW).Value 'billing address 5
Range("E5").Value = Sheet3.Range("I" & projROW).Value 'shipping address 1
Range("E6").Value = Sheet3.Range("J" & projROW).Value 'shipping address 2
Range("E7").Value = Sheet3.Range("K" & projROW).Value 'shipping address 3
Range("E8").Value = Sheet3.Range("L" & projROW).Value 'shipping address 4
Range("E9").Value = Sheet3.Range("M" & projROW).Value 'shipping address 5
Range("I10").Value = Sheet3.Range("N" & projROW).Value 'main phone
Range("I11").Value = Sheet3.Range("O" & projROW).Value 'other phone
Range("C10").Value = Sheet3.Range("P" & projROW).Value _
& " " & Sheet3.Range("Q" & projROW).Value 'main & other contact
Range("C11").Value = Sheet3.Range("R" & projROW).Value _
& " " & Sheet3.Range("S" & projROW).Value 'main & other email
Range("I12").Value = Sheet3.Range("T" & projROW).Value 'ship via
Range("H13").Value = Sheet3.Range("U" & projROW).Value 'ship notes
Range("C12").Value = Sheet3.Range("C" & projROW).Value 'customer
Else 'project not found
ActiveSheet.Shapes("EDITPRObttn").Visible = msoFalse
Range("B5:D9").ClearContents 'clear billing
Range("E5:E9").ClearContents 'clear shipping
Range("C10:C12").ClearContents 'clear customer info
Range("I10:J12").ClearContents 'clear phone & ship via
Range("H13:J13").ClearContents 'clear project fields
If MsgBox(" This client does not exist. Would you like to add them?", vbYesNo) = vbYes Then
Sheet4.Range("M1").Value = "NEW"
AddClientForm.proNAME.Value = Range("C13").Value 'new project name
AddClientForm.Show
End If
End If
End If
End Sub