Accessing a CommandButton via code 1

[BajanPOET]

How can I access a CommandButton's properties etc via code? I want to disable or hide the command button based on a condition (tested by an IF-THEN-ELSE statement) but I don't know how to access the CommandButton1.enabled or .visible properties using code

What? Who? ME????

 

[SkipVought]

Hi,

What application?

If Excel, on a sheet or in a UserForm?

Skip,
[sub]
[red][/red]
[/sub]

 

[BajanPOET]

It's on a sheet in Excel... sorry about that...


What? Who? ME????

 

[SkipVought]

with YourSheet.Shapes("YourCommandObjectName").OleFormat.Object
  if [i]expression[/i] then
     .Visible = true
  else
     .Visible = false
  end if
end with

Skip,
[sub]
[red][/red]
[/sub]

 

[BajanPOET]

How do I find out the name of YourCommandObjectName - would that be the CommandButton1 name? And YourSheet would be the mame of the sheet - LPO: so that I reference it by Sheets("LPO")?

What? Who? ME????

 

[SkipVought]

Select the Control Object and observe the name in the Name Box.

BTW, is this a Form control of a Control Toolbox control?

Skip,
[sub]
[red][/red]
[/sub]

 

[BajanPOET]

This is a Control Toolbox control. The name in the name box when I right click it and choose Properties is CommandButton1.

What? Who? ME????

 

[SkipVought]

For Control Toobox...

with CommandButton1
  if expression then
     .Visible = true
  else
     .Visible = false
  end if
end with

Skip,
[sub]
[red][/red]
[/sub]

 

[BajanPOET]

While stepping through my code after adding the above code snippet, I realized when I hovered my mouse over the CommandButton1 after the focus had passed the "with CommandButton1" code that CommandButton1 as an object appeared empty! (CommandButton1 = empty)

I will try it and see if it works, but I think this means that the code doesn't recognize the commandbutton... let u guys know what happens in a bit.

What? Who? ME????

 

[BajanPOET]

It's telling me "Object required" when it gets to the CommandButton's .Visible property. As I figured, it's not seeing the CommandButton.

What? Who? ME????

 

[SkipVought]

If the code is in a MODULE, then the command button needs a Sheet Object as the parent reference...

with YourSheetObject.CommandButton1
  if expression then
     .Visible = true
  else
     .Visible = false
  end if
end with

Skip,
[sub]
[red][/red]
[/sub]

 

[BajanPOET]

yes, that worked... thanks



What? Who? ME????