Disable a macrobutton for a particular cell change?
 

I am working on an excel database type file in which the master sheet is
used as data entry form and the values are stored in other sheets
designated for the particular month in the respective columns(one
column for one day)
Master sheet date is changed by calendar control and the calendar value
is used to get the name of the database sheet and the column.(December
2005, selected column etc)
After entering the values in the master sheet, the user either presses
a macro button named store or a toolbar button also named store.
User can also retrieve data by changing the date to required and
pressing a button named retrieve.
This is where the trouble starts! *If the user changes the date and
presses store instead of pressing retrieve, then the old data which is
already there in the database is lost!*
Is there anyway to disable the store button using the worksheet change
event, i.e., if the cell containing date is changed, the store button
should be disabled temporarily so that user can not by mistake click
it?
Hope I have explained my case clearly :confused:


--
lotus
------------------------------------------------------------------------
lotus's Profile: http://www.excelforum.com/member.php...o&userid=23759
View this thread: http://www.excelforum.com/showthread...hreadid=493216

Disable a macrobutton for a particular cell change?
 

Anything is possible and what you are asking is not too bad to do at all. A
couple of question though... The button on the worksheet you want to disable;
Is it from the forms toolbar or from the control toolbox. The code is
different for the two buttons. If the button is from the control toolbox then
it should be as simple as addin this to the Sheet Change event:

Sheet1.CommandButton1.Enabled = False

If it is from the Forms toolbar then I might be more inclined to change the
button to check if the Date is not what was expected and warn the user and
abort the rest of the code.

Another question is what change to the date should re-enable the button.
--
HTH...

Jim Thomlinson


"lotus" wrote:


I am working on an excel database type file in which the master sheet is
used as data entry form and the values are stored in other sheets
designated for the particular month in the respective columns(one
column for one day)
Master sheet date is changed by calendar control and the calendar value
is used to get the name of the database sheet and the column.(December
2005, selected column etc)
After entering the values in the master sheet, the user either presses
a macro button named store or a toolbar button also named store.
User can also retrieve data by changing the date to required and
pressing a button named retrieve.
This is where the trouble starts! *If the user changes the date and
presses store instead of pressing retrieve, then the old data which is
already there in the database is lost!*
Is there anyway to disable the store button using the worksheet change
event, i.e., if the cell containing date is changed, the store button
should be disabled temporarily so that user can not by mistake click
it?
Hope I have explained my case clearly :confused:


--
lotus
------------------------------------------------------------------------
lotus's Profile: http://www.excelforum.com/member.php...o&userid=23759
View this thread: http://www.excelforum.com/showthread...hreadid=493216

Disable a macrobutton for a particular cell change?
 

Hi Jim,
Thanks for the quick response. The trouble is not actually enabling or
disabling the macro button.
I am only searching for a way to find if the user changes the date,
then that should trigger this event. May be something like storing the
old date in a variable and comparing it with the changed date.
The button can be re-enabled when the user pressed retrieve or when the
date is changed to the old date which is stored as a variable!
:confused:
And yeah, it's a forms toolbar button!


--
lotus
------------------------------------------------------------------------
lotus's Profile: http://www.excelforum.com/member.php...o&userid=23759
View this thread: http://www.excelforum.com/showthread...hreadid=493216

Disable a macrobutton for a particular cell change?
 

Ok, I have solved it myself. *If there is a better method please tell me
guys*.

Also, I preferred to use a custom toolbar.

I added the following code to my *Retrieve *button:

'This stores the retrieved date in a distant cell(in my case it is
AF5:AH5)
'I have named the cells containing date as Date, Month & Year

With Sheets("Master")
..Range("AF5") = Range("Date")
..Range("AG5") = Range("Month")
..Range("AH5") = Range("Year")
End With

Then I added the following code to the *worksheet_calculate *event
procedu

If Range("Date") < Range("AF5") Or Range("Month") < Range("AG5") Or
Range("Year") < Range("AH5") Then
Application.CommandBars("My Tool Bar").Controls("Store").Enabled =
False
Else
Application.CommandBars("My Tool Bar").Controls("Store").Enabled =
True
End If

So, if the retrieved date does not match with the change in
calendar(which happens when the user changes the date) the *store
*button is disabled and the user will not click it accidentally and
spoil the database.
It is working fine for me.


--
lotus
------------------------------------------------------------------------
lotus's Profile: http://www.excelforum.com/member.php...o&userid=23759
View this thread: http://www.excelforum.com/showthread...hreadid=493216