JohnGow383
Board Regular
- Joined
- Jul 6, 2021
- Messages
- 141
- Office Version
- 2013
- Platform
- Windows
Hi.
I'm new to VBA and my below code works but I'm sure there is a more efficient way of writing it and I'm keen to learn.
This code works and it basically copies a comment from a fixed merged cell in the first sheet (NOON Logs) to cells in another sheet (LOG Entries) in the same workbook. It's fired by a worksheet change in the first sheet. Each comment gets a prefixed consecutive number. The comments will populate a table of up to 8 cells in the 2nd sheet. So say there are two comments already, the code will see they are populated and copy and paste the comment into the 3rd cell and so on. When 8 are full a message box will appear to say maximum number of comments has been logged.
I have also started with it auto deleting the 2nd comment if the last 6 characters of the 2nd comment match the first and want to expand this. As you cn see the length of the code is getting longer and longer so I was wondering if there is a more elegant way of writing this? Here is the whole code from the Worksheet_change private sub. This calls the other copy child macros.
Also, you can see I have made comments1 and 2 as string variables and assigned these to their respective ranges. The last line is used to check if comment 2 right 6 characters match comment 1 and then delete comment 2 if true. However I am having to use the whole range address to get this to work. When I use
It doesn't seem to work.
As mentioned above, this all works and does what I need it to do, but I'd like to know if there is a better way of writing this.
Thanks
I'm new to VBA and my below code works but I'm sure there is a more efficient way of writing it and I'm keen to learn.
This code works and it basically copies a comment from a fixed merged cell in the first sheet (NOON Logs) to cells in another sheet (LOG Entries) in the same workbook. It's fired by a worksheet change in the first sheet. Each comment gets a prefixed consecutive number. The comments will populate a table of up to 8 cells in the 2nd sheet. So say there are two comments already, the code will see they are populated and copy and paste the comment into the 3rd cell and so on. When 8 are full a message box will appear to say maximum number of comments has been logged.
I have also started with it auto deleting the 2nd comment if the last 6 characters of the 2nd comment match the first and want to expand this. As you cn see the length of the code is getting longer and longer so I was wondering if there is a more elegant way of writing this? Here is the whole code from the Worksheet_change private sub. This calls the other copy child macros.
VBA Code:
Dim DailyComment, ComRng1, ComRng2, ComRng3, ComRng4, ComRng5, ComRng6, ComRng7, ComRng8 As Range
Dim Comment1, Comment2 As String
Set ComRng1 = Sheets("LOG Entries").Range("T14")
Set ComRng2 = Sheets("LOG Entries").Range("T15")
Set ComRng3 = Sheets("LOG Entries").Range("T16")
Set ComRng4 = Sheets("LOG Entries").Range("T18")
Set ComRng5 = Sheets("LOG Entries").Range("T20")
Set ComRng6 = Sheets("LOG Entries").Range("T22")
Set ComRng7 = Sheets("LOG Entries").Range("T24")
Set ComRng8 = Sheets("LOG Entries").Range("T26")
Set DayCom = Range("H13")
Comment1 = ComRng1.Value
Comment2 = ComRng2.Value
If Not Application.Intersect(Range("H13"), Target) Is Nothing Then 'Message box is trigger cell
If Len(DayCom) > 6 And IsEmpty(ComRng1) = False And IsEmpty(ComRng2) = False And IsEmpty(ComRng3) = False And IsEmpty(ComRng4) = False And IsEmpty(ComRng5) = False And IsEmpty(ComRng6) = False And IsEmpty(ComRng7) = False And IsEmpty(ComRng8) = False Then MsgBox "Maximum Number of Comments Logged", vbExclamation, Title:="Comment Logging"
If Len(DayCom) > 6 And IsEmpty(ComRng1) = False And IsEmpty(ComRng2) = False And IsEmpty(ComRng3) = False And IsEmpty(ComRng4) = False And IsEmpty(ComRng5) = False And IsEmpty(ComRng6) = False And IsEmpty(ComRng7) = False And IsEmpty(ComRng8) = True Then Call CopyDailyComment8
If Len(DayCom) > 6 And IsEmpty(ComRng1) = False And IsEmpty(ComRng2) = False And IsEmpty(ComRng3) = False And IsEmpty(ComRng4) = False And IsEmpty(ComRng5) = False And IsEmpty(ComRng6) = False And IsEmpty(ComRng7) = True Then Call CopyDailyComment7
If Len(DayCom) > 6 And IsEmpty(ComRng1) = False And IsEmpty(ComRng2) = False And IsEmpty(ComRng3) = False And IsEmpty(ComRng4) = False And IsEmpty(ComRng5) = False And IsEmpty(ComRng6) = True Then Call CopyDailyComment6
If Len(DayCom) > 6 And IsEmpty(ComRng1) = False And IsEmpty(ComRng2) = False And IsEmpty(ComRng3) = False And IsEmpty(ComRng4) = False And IsEmpty(ComRng5) = True Then Call CopyDailyComment5
If Len(DayCom) > 6 And IsEmpty(ComRng1) = False And IsEmpty(ComRng2) = False And IsEmpty(ComRng3) = False And IsEmpty(ComRng4) = True Then Call CopyDailyComment4
If Len(DayCom) > 6 And IsEmpty(ComRng1) = False And IsEmpty(ComRng2) = False And IsEmpty(ComRng3) = True Then Call CopyDailyComment3
If Len(DayCom) > 6 And IsEmpty(ComRng1) = False And IsEmpty(ComRng2) = True Then Call CopyDailyComment2
If Len(DayCom) > 6 And IsEmpty(ComRng1) = True Then Call CopyDailyComment1
If IsEmpty(ComRng1) = False And Right(Sheets("LOG Entries").Range("T14").Value, 6) = Right(Sheets("LOG Entries").Range("T15").Value, 6) Then Call ClearCom2 'If the same 2nd comment has been re-entered it will be deleted but leave the first.
VBA Code:
If IsEmpty(ComRng1) = False And Right(Comment1, 6) = Right(Comment2, 6) Then Call ClearCom2
As mentioned above, this all works and does what I need it to do, but I'd like to know if there is a better way of writing this.
Thanks