How do I clear 'form' content from an unprotected cell without mac

[Casey M]

Either this isn't possible or I am searching the web/forum using wrong search
terms.

I have created a very robust 'timesheet' form which allows users to select a
radio button "bi-weekly" or "bi-monthly". This clears the "work period
ending" date field using a macro. (the "work period ending" field is used to
fill the rest of the dates (previous 14 days for bi-weekly, 13-16 days for
bi-monthly) I have other 'bells and whistles' to ensure it is error/dummy
proof so it can be deployed and used by any level of user. But the one issue
I can't get around is how to clear the value of a different cell without
using a macro. I don't want macros because, if a user doesn't enable macros,
I don't want the unexpected behavior.

I am basically trying to:

In cell A1
clear the contents in cell C1 if a change in vallue B1 from "Bi-Monthly" to
"Bi-Weekly" or from "Bi-Weekly" to "Bi-Monthly" occurs. If no change, don't
reset the form.

I have wracked my brain on this for many hours.... I have never tried to
effect the content of an external cell based on formula of the current cell
(without VB, a macro or a formula in the 'external' cell itself.... I don't
even know if Excel allows it.

Thanks in advance for expert advice. Reading the other forum responses
seeking an answer I have learned quite a bit... most importantly, that if
anyone can help it is you guys!

Casey

 

[Casey M]

Thanks for the response.

In my example:
C1 would be a data entry cell.
A1 is a 'hidden' cell containing the formula that watches cell B1 for
activity (trigger)
B1 uses data validation list to force user to select "Bi-monthly" or
"Bi-weekly" from list
This file has not been sent to anyone, so I have no idea if they do or don't
have macros enabled. I set it up to work using macros because this is the
way I know how to do it, but I do not want to send this timesheet to others
until it works without macros.

I have done some pretty crazy things using formulas, data validation,
conditional formatting, etc... hopefully someone else has a creative work
around. I'm not ready to give up yet!

Casey

Casey

 

[Ron@Buy]

Have you thought about setting up your workbook as a template?
Users will have to save their completed sheets with another filename and the
template remains blank ready for the next set of entries

 

[Ron@Buy]

I probably can't help you but reading thro' your problem some questions come
to mind.
What is in cell C1, is there a formula or is it a data entry cell?
What is the relevance of A1 to C1?
If you already have a macro running to clear the "work period ending" surely
the user has enabled macros?
Presumably you wish to link C1 to the clearing of the "work period ending"
Incidently you can't effect the contents of a remote cell without using a
macro unless you have a formula in that remote cell that refers to to data
elsewhere.
Wish you luck with your problem.

 

[Gord Dibben]

You need a contingency plan that renders the workbook useless if users do
not enable macros.

Here is a sample................

Create a sheet named Dummy with a large message typed in the middle.

"You have disabled Macros and this workbook is useless without them. Please
close and re-open with macros enabled"

Then add these two event codes to Thisworkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dummy").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Dummy" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Dummy" Then
sht.Visible = True
Sheets("Dummy").Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP