Event Handler based on one cell change

leatherhen99

New Member
Joined
Dec 17, 2019
Messages
21
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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,339
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,570
Messages
5,770,913
Members
425,652
Latest member
Pemby

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