Why Doesn't this hide my command button?

Rickinnocal

New Member
Joined
Dec 14, 2010
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Hello, I have a command button on a worksheet that creates and populates a new workbook. The new workbook is required only for one specific client, so if the job is for a different client, I'd like the command button to be hidden.

The name of the client is in cell E5.

This is my code...

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

    Application.ScreenUpdating = False
    If Cells(6, 5).Value = "CMA CGM" Then
        Me.cmdCMAeFile.Visible = True
    Else
        Me.cmdCMAeFile.Visible = False
    End If
    Application.ScreenUpdating = True
   
End Sub

No error messages, but the button is always visible.

The name of the button is correctly spelled, as the sub

VBA Code:
Private Sub cmdCMAeFile_Click()

functions exactly as it should.

What am I doing wrong, please?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
This is working for me. IS the macro in the worksheet module? What version of excel are you using?

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        
    'Application.ScreenUpdating = False
    If Cells(6, 5).Value = "CMA CGM" Then
        Me.CommandButton1.Visible = False
    Else
        Me.CommandButton1.Visible = True
    End If
    'Application.ScreenUpdating = True


End Sub


Private Sub CommandButton1_Click()

MsgBox "bye"
End Sub
 
Last edited:

Rickinnocal

New Member
Joined
Dec 14, 2010
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Now I'm even more confused.

Excel version is Excel for 365, latest update.
Yes, the Macro is in the Worksheet Module. I can right click on the sheet tab, select "View Code", and see it above my command button code.

I opened a brand new workbook, but a CommandButton1 on it, put your code into the module, and it works perfectly.
I copied your code into the module for my workbook, changed CommandButton1 to cmdCMAeFile and nothing happens.
I changed the code back to CommandButton1 and added a CommandButton1 to the sheet, and nothing.
 

Rickinnocal

New Member
Joined
Dec 14, 2010
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Got it! Posted for anyone else who is looking at the same issue...

The problem is that cell E6 doesn't actually contain the Client name. It contains "='Data Entry'!B1" and fills from that sheet. Worksheet_Change doesnt see it as changing when a change is made to 'Data Entry'!B1

If I overtype E6 with an actual client name, the button appears and disappears as it should.

Now got to figure out a way round that.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Excel version is Excel for 365, latest update.
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If B1 on the data entry sheet is changed manually, why not put the change event in the Data Entry sheet & refer to the button like
VBA Code:
Sheets("Sheet1").cmdCMAeFile.Visible
changing sheet name to suit.
 

Rickinnocal

New Member
Joined
Dec 14, 2010
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Thanks, I updated my account details.

Since I'd already got that code, I left it as was and just added a target address of Data Entry B1

Thanks for the help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,136,952
Messages
5,678,745
Members
419,782
Latest member
gc75150

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