Event Handler based on one cell change

leatherhen99

New Member
Joined
Dec 17, 2019
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I know that this is probably an easy fix, but Event Handlers are not my language! I have an evaluation form that someone will fill out... there might be times that they have to load a previous evaluation and make updates... so... if cell D2 has a leader's name in it (it's a dropdown field), I want the (load button) "btn_Load" shape to be invisible... but if the field is empty, I want the button to be available.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Range("D2")

If Not Intersect(Target, rng) Is Nothing Then

        Shapes("btn_Update").Visible = msoFalse
        Shapes("btn_Load").Visible = msoFalse
        Shapes("btn_Clear").Visible = msoCTrue
        Shapes("btn_Submit").Visible = msoCTrue
  Else
        Shapes("btn_Update").Visible = msoFalse
        Shapes("btn_Load").Visible = msoCTrue
        Shapes("btn_Clear").Visible = msoCTrue
        Shapes("btn_Submit").Visible = msoCTrue

  End If

Set rng = Nothing

End Sub

Right now, it will remove the load button if a name is selected, but if the field is cleared, it won't put the button back... If I go change another cell, the button will appear after the other cell is changed, but not immediately when D2 is empty.

Suggestions? Recommendations?

Thank you :)
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,804
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Range("D2")

If Not Intersect(Target, rng) Is Nothing Then
   If Target.Value <> "" Then
        Shapes("btn_Update").Visible = msoFalse
        Shapes("btn_Load").Visible = msoFalse
        Shapes("btn_Clear").Visible = msoCTrue
        Shapes("btn_Submit").Visible = msoCTrue
  Else
        Shapes("btn_Update").Visible = msoFalse
        Shapes("btn_Load").Visible = msoCTrue
        Shapes("btn_Clear").Visible = msoCTrue
        Shapes("btn_Submit").Visible = msoCTrue

  End If
End If
Set rng = Nothing

End Sub
 
Upvote 0

Forum statistics

Threads
1,187,188
Messages
5,962,108
Members
438,585
Latest member
peterLEE0223

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
Top