Changing the Value of a Cell When a User Clicks a Specific Cell - Need Help With Efficiency

[Polar]

Hi there! I have a program that I'm trying to write that displays a date in a cell based on whether a user clicks on a specific cell.

Here are a few notes:
The date is formatted as YYYMMDD
A4 - Contains the first half of the date (ex YYYYMM)
M4 - Displays the final date in YYMMMDD format
F/G5 - Cell that the user will click to trigger change in cell M4
F/G4 - Cell that contains the number that will complete the day portion of the "date"

This is just a smaller test for just two cells, however this will be used on 18 cells...so I imagine this code will start getting really big. Rather than copying the same code over 18x, does anyone know of any ways to make this shorter, and more efficient?

Here's what I put together so far:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  Application.EnableEvents = False
  
With Target
    If .Address = Range("F5").Address Then
         If IsEmpty(Range("F5").Value) = True Then
            'do nothing
        Else
             If Range("F4").Value < 10 Then
                Sheet1.Range("M4").Value = Sheet1.Range("A4").Value & "0" & Sheet1.Range("F4").Value
             Else
                Sheet1.Range("M4").Value = Sheet1.Range("A4").Value & Sheet1.Range("F4").Value
            End If 
         End If 
    Else
         If .Address = Range("G5").Address Then
             If IsEmpty(Range("G5").Value) = True Then
                'do nothing
             Else
                 If Range("G4").Value < 10 Then
                    Sheet1.Range("M4").Value = Sheet1.Range("A4").Value & "0" & Sheet1.Range("G4").Value
                 Else
                    Sheet1.Range("M4").Value = Sheet1.Range("A4").Value & Sheet1.Range("G4").Value
                End If 
             End If 
         Else
            'repeat code above for other range
         End If 
     End If 
End With
Application.EnableEvents = True
End Sub

Any help/tips appreciated!

 

[DanteAmor]

Let's start with this, try the following, it's a breakthrough, since in both cells you update M4 and take the value of A4, I really don't know if the other cells are down or to the right, you could comment.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  If Target.Count > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  On Error GoTo appEvents
  Application.EnableEvents = False
  If Not Intersect(Target, Range("F5:Z5")) Is Nothing Then
    If Target.Offset(-1).Value < 10 Then
      Sheet1.Range("M4").Value = Sheet1.Range("A4").Value & "0" & Sheet1.Cells(4, Target.Column).Value
    Else
      Sheet1.Range("M4").Value = Sheet1.Range("A4").Value & Sheet1.Cells(4, Target.Column).Value
    End If
  End If
appEvents:
  Application.EnableEvents = True
End Sub

 

[Eric W]

Welcome to the forum!

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    
    If Intersect(Target, Range("F5:X5")) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    
    Application.EnableEvents = False
    Range("M4").Value = Range("A4").Value & Format(Target.Offset(-1).Value, "00")
    Application.EnableEvents = True

End Sub

Note that the range in the Intersect line can also look like:

    If Intersect(Target, Range("F5,G5,J5:L5")) Is Nothing Then Exit Sub

So you can identify your 24 cells in some method.

Also, you can use the Format function to eliminate a few lines.

 

[Polar]

Thank you both for taking the time to review and answer my question! It's way more compact that what I had LOL. I'm still fairly new to VBA (and programming) so I'm hoping my code will start to look a bit more like what you've provided.

To clarify with my request:
- There is only one cell that is going to be updated--and that is M4. A4 Will remain static.
- The other 30 cells (not 18...anymore) are one row apart from each other. Ex. H8:L8, then H10:L10, H12:L12...to H18:L18

I have some other related questions as well (more out of curiosity/trying to understand the code), but I will address those individually in separate replies.

 

[Polar]

Thank you DanteAmor!

A few questions:
- I'm not quite too sure what the Target.Count >1 portion does...could you elaborate?
- could the Intersect function be used in he same way to specify the ranges that I mentioned in my previous reply? (H8:L8...H18:L18)

 

[DanteAmor]

Thank you both for taking the time to review and answer my question! It's way more compact that what I had LOL. I'm still fairly new to VBA (and programming) so I'm hoping my code will start to look a bit more like what you've provided.

To clarify with my request:
- There is only one cell that is going to be updated--and that is M4. A4 Will remain static.
- The other 30 cells (not 18...anymore) are one row apart from each other. Ex. H8:L8, then H10:L10, H12:L12...to H18:L18

I do not understand.
Which cell will be modified and which cells will be used.
In your macro you had F5 and G5, but now you put H8:L8

I have some other related questions as well (more out of curiosity/trying to understand the code), but I will address those individually in separate replies.

Could you comment

 

[Polar]

Thanks Eric, glad to be here

Could I use a method to do specify the ranges that I need (mentioned in prev post) in a method? Would that look like:

 If Intersect(Target, Range("H8:L8, H10:L10...ect")) Is Nothing Then Exit Sub

Also, is there a difference between using IsEmpty and Is Nothing?

 

[Eric W]

Do you still want to look at the F5 or G5 cell? Is the upshot of your question that if someone selects one of these cells: H10:L10, H12:L12, H14:L14, H16:L16, H18:L18, you want to put a value in M4 based on the cell right above the selected cell? If so then my original version works, just change the Intersect line:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    
    If Intersect(Target, Range("H10:L10,H12:L12,H14:L14,H16:L16,H18:L18")) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    
    Application.EnableEvents = False
    Range("M4").Value = Range("A4").Value & Format(Target.Offset(-1).Value, "00")
    Application.EnableEvents = True


End Sub

You could also change that single line to:

    If Intersect(Target, Range("H10:L18")) Is Nothing Then Exit Sub
    If Range.Row Mod 2 = 1 Then Exit Sub

which might be a bit easier to change in the future. The second If will exit if the row number is odd.

 

[Eric W]

IsEmpty is a method to see if there is something in a cell. In my macro I used:

    If Target.Value = "" Then Exit Sub

to handle that.

"Nothing" has a completely different purpose. The Intersect method is used to find the overlapping range of 2 different ranges. So if you try Intersect(Range("A1:D10"),Range("B2:E4")) the result would be another range: B2:D4. If the 2 ranges do not overlap at all, you don't get a range. You get Nothing. So that's how Dante and I decide whether to process your target cell or not, if the target intersects with the range we gave it. This is usually a better way to decide than looking at the target's address like you did in the original question. That can work, but if you want to look at a large disjoint range, the If's can get complicated.


Edit: In my previous post, the last code should be:

    If Intersect(Target, Range("H10:L18")) Is Nothing Then Exit Sub
    If Target.Row Mod 2 = 1 Then Exit Sub

 

[Polar]

Yikes. My mistake. I was looking at the wrong rows...And I should probably clarify that its every other row. It should be F5:J5, F7:J7, F9:J9, F11:J11, F13:J13, F15:J15 for the whole set. So cells F5/G5/H5/I5/J5 are being checked (same with all other rows mentioned) Does that make more sense?