help with vba and command button

R

RichardO

Hi, I have a macro called create_report
I would like to insert this in a command button. I want the comman
button to only be clicked once when the sheet is open, and it should b
disabled afterwards. So, if the sheet is closed and reopened, th
button should work once and then be disabled. I have the following
it's not making my button work at all:

Private Sub CommandButton1_Click()
Create_Report
CommandButton1.Enabled = False
End Sub


Please help.


richard
 
B

Bob Phillips

Have you put the code in the worksheet code module?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

Gene Belknap

Hi Richard

You can have the workbook_open event fire to re-enable your command button each time. For example, assuming your button is on "Sheet1" you could have code like the following store in the "ThisWorkbook" code module

Private Sub Workbook_Open(
Sheet1.CommandButton1.Enabled = Tru
End Su

The other existing code you have would do the job and then disable the button. This would re-enable the button whenever the workbook was opened

-Gen


----- RichardO > wrote: ----

Hi, I have a macro called create_repor
I would like to insert this in a command button. I want the comman
button to only be clicked once when the sheet is open, and it should b
disabled afterwards. So, if the sheet is closed and reopened, th
button should work once and then be disabled. I have the following
it's not making my button work at all

Private Sub CommandButton1_Click(
Create_Repor
CommandButton1.Enabled = Fals
End Su


Please help


richard
 
R

RichardO

Hi Gene,

I tried that, it disabled the command button after running the cod
once. Then I closed the file, re-opened it, but the button i
disabled, I can't run the code again. The reason why I want th
button to work when I reopen the file is because I made use of the fil
everyday, but then I save it as another name and then work on it.

How do I modify the code so that when I reopen my excel workbook, th
button is enabled.

This is the code I used when I clicked ThisWorkbook:

Private Sub Workbook_Open()
Sheet1.CommandButton1.Enabled = True
End Sub

This is the macro to run the report in sheet 2 because my data is i
sheet 2:

Private Sub CommandButton1_Click()
Create_Report
CommandButton1.Enabled = False
End Sub

Thanks,


Richardo
 
R

RichardO

Hi Gene:

I tried all 3 points. the applications.enableevents= true
My code was saved, and it was saved in sheet2, because my button is in
sheet 2. so I changed the thisworkbook code from:

Private Sub Workbook_Open()
Sheet1.CommandButton1.Enabled = True
End Sub

to

Private Sub Workbook_Open()
Sheet2.CommandButton1.Enabled = True
End Sub

But it's still not activating the button. I also changed the "code
name", same thing.

I don't mind taking a look at the macro you set up. Please send it to:
[email protected]

Thanks.

Richardo
 
Top