Why Doesn't this hide my command button?

[Rickinnocal]

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...

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

Private Sub cmdCMAeFile_Click()

functions exactly as it should.

What am I doing wrong, please?

 

[mrshl9898]

This is working for me. IS the macro in the worksheet module? What version of excel are you using?

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

 

[Rickinnocal]

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]

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]

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

Sheets("Sheet1").cmdCMAeFile.Visible

changing sheet name to suit.

 

[Rickinnocal]

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]

You're welcome & thanks for the feedback