Help creating formula in VBA

[HansV]

Yes, if you unhide Personal.xlsb, you can edit its macros from the Macros dialog.
But I tend to forget to hide Personal.xlsb again before saving, so next time I start Excel, the personal macro workbook will come up visible.
So I prefer to open the Visual Basic Editor and edit the macros from there; in fact I seldom use the Macros dialog.

[Asher]

Hans,

Thanks so much for your help with the formula. It works great! I have run into one issue, I have to keep the sheet protected, so when the formula goes to update it tells me I need to unprotect it. I know this can be put into the sub but I was wondering if you could help me with the syntax? I was thinking it should be something like:

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheet.Unprotect "password"
With Range("D49:O49")
  .Formula="=IFERROR(IF('Sheet 2'!$N$1>=D$47,'Sheet 2'!AF303,""""),"""")"
  .Value = .Value
End With
Sheet.Protect "password"

But I'm not sure about the syntax cause I keep getting an "Object Required" error.

Do you know why?

[StuartR]

Try replacing Sheet.Unprotect and Sheet.Protect with ActiveSheet.Unprotect and ActiveSheet.Protect

Or you could use
Target.Parent.Unprotect and Target.Parent.Protect

[HansV]

Instead of Sheet.Unprotect and Sheet.Protect, use Me.Unprotect and Me.Protect. In a worksheet module, Me refers to the worksheet.

Anmother option would be to protect the worksheet in the Workbook_Open event in the ThisWorkbook module with UserInterfaceOnly:=True. This means that VBA code can modify the sheet without unprotecting it.

Code: Select all

Private Sub Workbook_Open()
  Me.Worksheets("Sheet 1").Protect Password:="password", UserInterfaceOnly:=True
End Sub

Substitute the correct name. In the ThisWorkbook module, Me refers to the workbook as a whole.

Do you really want the code to run whenever the user moves to another cell?

[Asher]

Substitute the correct name. In the ThisWorkbook module, Me refers to the workbook as a whole.

Do you really want the code to run whenever the user moves to another cell?

Um, no. It just looks like it runs every time I click on one of the cells that it refers to. So, if they unprotect it, then accidently click on one of those cells, it will run that unprotect and shoot an error, so I think your idea about the UserInterfaceOnly:=True type of protection is a great idea! I've read about it before, but have been afraid to use it without asking someone who's used it if it does anything unexpected that I should be aware of first.

I am always hesitant when first using a new command cause once I accidentally turned off all my right click paste functions for a long time and it was heck finding the code to put them back on :-)

So, where do I find the "ThisWorkbook Module to put this code in to?"

[HansV]

So, where do I find the "ThisWorkbook Module to put this code in to?"

In the Visual Basic Editor:

x210.png

Specifying UserInterfaceOnly:=True doesn't have negative side-effects.

When exactly would you like your code to run?

[Goshute]

I don't know if I'm maybe oversimplifying what seems to have become an extremely complex matter, but, why don't you just unhide your personal.xlsb? Once you do that you can edit the macros in it and you don't have to make any new books or anything. Then just hide it again when you are done?

It's not necessary to unhide personal.xls* to edit the macros in it - you can access them for edit/addition through Alt-F11 and navigate to the personal.xls* modules at any time.

[Asher]

When exactly would you like your code to run?

Just when the data is relevent based on the formula. Basically the formula checks a Master Date cell in sheet 2 with the cells in sheet 1 that contain all the months of the year, it sees if the date is less than or equal to the Master date, then shows the data in the cell based on the rest of the calculation in the formula.

This is necessary because sometimes there is data before the date I want to show and dat is often input prematurely and is therefore incomplete (as it is based on other date related formulas).

[HansV]

What exactly is the range of cells that - if changed - should trigger the code to run? Is it D47:O47?

[Asher]

What exactly is the range of cells that - if changed - should trigger the code to run? Is it D47:O47?

It's actually the range from sheet 2 that has the changing data AF303:AQ303.

But I only want to see the data if the Master Date (Sheet 2 $N$1) is > = the date(s) listed in cells D47:O47. So I'm actually filling an array by month up to and including the Master date for a period of one year. D47=Oct 2010 E47= Nov 2010 etc...

So if the Master Date is set to Dec 2010 I will see the data from sheet 2 AF303, AG303, and AH303 in sheet 1 D49, E49, F49

I hope I explained that right.

[HansV]

So the code should run when you change N1 on Sheet 2, and also if you change a cell in the range AF303:AQ303 on Sheet 2?

[Asher]

Indeed.

[HansV]

In that case, you should not use code in the worksheet module of the sheet where you want the values, but in the worksheet module of Sheet 2. We'll use the Workseet_Change event, not Worksheet_SelectionChange. Let's say that the sheet where you want the values is Sheet 1.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("N1,AF303:AQ303"), Target) Is Nothing Then
    With Worksheets("Sheet 1")
      .Unprotect "password"
      With .Range("D49:O49")
        .Formula="=IFERROR(IF('Sheet 2'!$N$1>=D$47,'Sheet 2'!AF303,""""),"""")"
        .Value = .Value
      End With
      .Protect "password"
    End With
  End If
End Sub

Again, this code is for Sheet 2.

[Asher]

Instead of Sheet.Unprotect and Sheet.Protect, use Me.Unprotect and Me.Protect. In a worksheet module, Me refers to the worksheet.

Anmother option would be to protect the worksheet in the Workbook_Open event in the ThisWorkbook module with UserInterfaceOnly:=True. This means that VBA code can modify the sheet without unprotecting it.

Code: Select all

Private Sub Workbook_Open()
  Me.Worksheets("Sheet 1").Protect Password:="password", UserInterfaceOnly:=True
End Sub

Substitute the correct name. In the ThisWorkbook module, Me refers to the workbook as a whole.

Just to clarify, I substitute just the correct sheet name and still use Me to reference the workbook?
Also, if I protect the sheet like this, can I still unprotect it at the tab level to make adjustments to other things and then it will just reprotect it when it opens next?

[Asher]

Ok, I just saw your post about the code for Sheet 2. Wow, you are so quick! I just finish typing and you already answered and I'm behind again. So using the code for sheet 2 means I won't need the code for Workbook_Open event?

[HansV]

Just to clarify, I substitute just the correct sheet name and still use Me to reference the workbook?
Also, if I protect the sheet like this, can I still unprotect it at the tab level to make adjustments to other things and then it will just reprotect it when it opens next?

You only need to substitute the correct sheet name for "Sheet 1".

If you unprotect the sheet manually, it will automatically be protected again next time the workbook is opened if the user enables macros.

[HansV]

Ok, I just saw your post about the code for Sheet 2. Wow, you are so quick! I just finish typing and you already answered and I'm behind again. So using the code for sheet 2 means I won't need the code for Workbook_Open event?

If you protect the sheet in Workbook_Open, you don't have to unprotect/protect it in the Worksheet_Change code.
Conversely, if you unprotect/protect it in the Worksheet_Change code, you don't need the Workbook_Open code. The choice is yours.

[Asher]

1. In protecting the sheet in the VBA, can I still somehow allow the cells to be selected w/o getting an error?

2. If I do the Sheet 2 code, does the Intersect just check to see if those cells have changed or is it actually checking to see if they have intersecting data?

Sorry I seem so ignorant, I've been working in macros and even adjusting them for a while now, and I've written a few simple subs, but getting objects to relate and syntax is killing me. I even took a VBA course but it was wrong, not Excel, VBA.net, not helpful too much in Excel. I have a book by J. Walkenbach and it helps but goes from beginner to expert in 5 pages flat :-) I really appreciate you helping me out.

[HansV]

1. By default, the user can select both locked and unlocked cells in a protected sheet. If you don't want the user to be able to select locked cells, add a line

.EnableSelection = xlUnlockedCells

or

Me.Worksheets("Sheet 1").EnableSelection = xlUnlockedCells

depending on where you protect the sheet.

2. Target is the range consisting of all cells that have directly been changed by the user. The Intersect is used to check whether any of the relevant cells have been changed,

[Asher]

Ok, I want to use the sheet 2 code, but I already have a Worksheet_Change Sub in sheet 2.

-Do I add the code to the existing Sub, or make a new one? Can it have 2?