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! :)
 
Well I'm still lost, I hope Eric understands.


If you explain the following, it may be clearer to me.


If I select cell F5, what should happen?
If I select G5, what should happen?
If I select H5, what should happen?
If I select I5, what should happen?
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Well, you should be able to adapt the first version of post 8 easy enough, just put your correct values in the Range("...") part of the red line. Try it and let us know if you have trouble. The 2-line version should be:

Rich (BB code):
    If Intersect(Target, Range("F5:J15")) Is Nothing Then Exit Sub
    If Target.Row Mod 2 = 0 Then Exit Sub
which has a shorter Intersect line, then adds another line to skip even rows.
 
Upvote 0
THIS IS IT! Thank you so much Eric ♫꒰・‿・๑꒱
It works like a charm. Thank you so much for all your help with this.

Quick question:
I ran into a compile error (argument not optional) with this line:

Code:
 If Range.Row Mod 2 = 0 Then Exit Sub

But then I noticed you changed it to:

Code:
 If Target.Row Mod 2 = 0 Then Exit Sub

I guess my last question would be...can you change the former to:

Code:
 If Range("insert range here").Row Mod 2 = 0 Then Exit Sub

and still get the same result? I guess the key is that you wont have to keep updating range "address" if you use Target.Row, right?
 
Upvote 0
You are correct on both counts. If you use Range, you need to give it an actual range, like Range("B17"), and that will work as you surmise. But you're also correct in that if you use Target, you don't have to worry about putting in a hard-coded range. Target will always points to the selected cell.

Glad we could help! :)
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

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