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

Polar

New Member
Joined
Oct 9, 2019
Messages
6
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:
Rich (BB code):
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! :)
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.


Code:
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
 
Upvote 0
Welcome to the forum!

Try this:

Code:
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:

Code:
    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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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:

Code:
 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?
 
Upvote 0
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:

Rich (BB code):
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:
Rich (BB code):
    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.
 
Upvote 0
IsEmpty is a method to see if there is something in a cell. In my macro I used:

Code:
    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:

Code:
    If Intersect(Target, Range("H10:L18")) Is Nothing Then Exit Sub
    If Target.Row Mod 2 = 1 Then Exit Sub
 
Last edited:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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