ThisWorkbook

  • Thread starter Thread starter DejaVu
  • Start date Start date
D

DejaVu

I have a workbook with one worksheet in it that I use as a template to
create a new sheet for each of my sales managers. In *ThisWorkbook*
(in my template) I have some coding. It's on the workbook level and
does a few various things such as; depending on which cell they have
selected, Range("A1") will say various things. All of this coding in
*ThisWorkbook* is saved in my template book... I want it to be in each
workbook created for each sales manager.
Here is a snippet of my code:
-Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)

Select Case Target.Row
Case Is = 3
Range("B20").Formula = "=if(B3="""","""",1-(B23/B3))"-

I'm not even sure if this is possible, or if there is an easier way to
do what I'm wanting... TIA
DejaVu
 
Can you not just place the formula in the cell? Since the sheet is a
template, I assume the user only needs access to specific areas, so you can
protect the formulas using sheet protection. Clear the check in the "Locked"
option of the Protection tab in FORMAT > CELLS... for all cells that require
user access. This requires no code.

Here's a formula that does what you want:

=IF(AND($B$3<>"",$B$23<>""),1-($B$23/$B$3),"")

It will make sure both cells have values, which will prevent a result of 0
(zero). Otherwise, =IF($B$3<>"",1-($B$23/$B$3),"")

Regards,
GS
 
Back
Top