Denying a cell to be changed in any way

[Rejje]

Hi! Problem for a learning vba-newbie:

1. I need a code that returns the full name of the active workbook into a cell named V_50300.

2. It should also deny the user to change anything within a cell named EXP_1010 if Range("V_50200").Value = Range("V_50300").Value.

The reason for this is that the full name of the active workbook can be depending on the text in EXP_1010.

If above is true and one tries to alter EXP_1010 then MsgBox "Changes to this cell is not allowed!"

3. This operation should always be running.

In case someone comes up with a solution: Where do I place the code?

[Simon Lloyd]

Hi! Problem for a learning vba-newbie:

1. I need a code that returns the full name of the active workbook into a cell named V_50300.

2. It should also deny the user to change anything within a cell named EXP_1010 if Range("V_50200").Value = Range("V_50300").Value.

The reason for this is that the full name of the active workbook can be depending on the text in EXP_1010.

If above is true and one tries to alter EXP_1010 then MsgBox "Changes to this cell is not allowed!"

3. This operation should always be running.

In case someone comes up with a solution: Where do I place the code?

This will (as long as macros are enabled) not let someone edit A1[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
MsgBox "You may not edit this cell", vbInformation, "Uneditable Cell"
Me.Range("A2").Select
End Sub[/VBA]To use this code right click the sheet tab that you want to use it on, choose view code and paste it in.

To get the fullname of the workbook in the named cell put this in the Thisworkbook code module (Alt+F11)[VBA]Private Sub Workbook_Open()
Range("V_50300").Value = ThisWorkbook.FullName
End Sub[/VBA]

[Rejje]

This will (as long as macros are enabled) not let someone edit A1[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
MsgBox "You may not edit this cell", vbInformation, "Uneditable Cell"
Me.Range("A2").Select
End Sub[/vba]To use this code right click the sheet tab that you want to use it on, choose view code and paste it in.

To get the fullname of the workbook in the named cell put this in the Thisworkbook code module (Alt+F11)[vba]Private Sub Workbook_Open()
Range("V_50300").Value = ThisWorkbook.FullName
End Sub[/vba]

Thanks! Everything work just fine, except for one detail. This is a detail that has bothered my whole project since I started adding the macros.

Here's the problem:

The workbook is a template until one presses a button. Then it will save as something else. Code below:

[vba]Sub SaveFile()
Dim wb As Workbook
Set wb = ActiveWorkbook

If Range("V_10300") = True Then

If UCase(wb.FullName) = UCase(Range("V_50100").Value) Then
MsgBox (UCase(Range("KUNDPOST_NAMN").Value) & " will be created!")
ActiveWorkbook.SaveAs Range("V_50200").Value
Workbooks.Open wb.FullName
wb.Save
Else
wb.Save
End If

Else
MsgBox (UCase("Cannot save - name is missing!"))
'The user has to write something in EXP_1010 which will be a part of the new name for the workbook
Range("EXP_1010").Select
End If

Set wb = Nothing

End Sub[/vba]

When i run

[VBA]

Private Sub Workbook_Open()
Range("V_50300").Value = ThisWorkbook.FullName
End Sub

[/VBA]

it will still say "Template" instead of Range("V_50200").Value which is where the name is stored that the file should be named when running above "save as".

Of course I get the right name of the file in V_50300 if a close and reopen. But, I would like for above Private Sub to correct the filename something like:

[VBA]
Private Sub Workbook_Open()
Run.Whenever.Any.Other.Macro.Has.Run
Range("V_50300").Value = ThisWorkbook.FullName
End Sub
[/VBA]

Is this possible?

[Simon Lloyd]

There isn't a name assigned to the workbook until after the save (remember a template is unalterable!), so unless you collect the name of the newly created workbook it will always show TEMPLATE.

[Rejje]

Yes, of course the template should never be possible to alter and is write-protected.

But how do I pick up the newly created "SOMETHING-EXP_1010.xlsm" (which is also "V_50200") file name into cell V_50300 when save as has just occured?

With this event history (newly created "SOMETHING-EXP_1010.xlsm" has not been closed and reopened) Excel says name of the workbook is "SOMETHING-EXP_1010.xlsm" but in cell V_50300 it says "TEMPLATE".

I really need to solve this! My whole project sort of depend on solving this problem for many reasons. Is there ANY way of solving this? Maybe one can add in Sub SaveFile close/reopen?

[Sean.DiSanti]

why don't you just protect the sheet with a pass? set the cells you want them to be able to edit to not be locked, and lock that cell. if you need to change it programatically you can do so by doing like
[VBA]ActiveSheet.Unprotect("YourPass")
activesheet.range("A1").formula = "whatever"
ActiveSheet.Protect("YourPass")[/VBA]

[Rejje]

I see what you are saying and it's allready done. When it comes to working directly in Excel I am irreproachable (or at least would like to think so).

However this particular cell must or must not be locked depending on circumstances.

EXP_1010 must not be locked if filename = "TEMPLATE.xlsm"
EXP_1010 must be locked if filename = "SOMETHING-EXP_1010.xlsm"

Opening the template EXP_1010 is of course not looked. But at the moment is been saved as "SOMETHING-EXP_1010.xlsm" i want it to be locked. This is why it's critical that I get this right!

[Sean.DiSanti]

so base your protection on the name; use the workbook open and before save events. you can also set your cell value to the workbook name before save, and protect it then
[vba]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ThisWorkbook.Name <> "TEMPLATE.xlsm" Then ActiveSheet.Protect ("Password")
End Sub

Private Sub Workbook_Open()
If ThisWorkbook.Name <> "TEMPLATE.xlsm" Then ActiveSheet.Protect ("Password")
End Sub[/vba]

[Bob Phillips]

You should not have code in your template, the template is just a data file. I think you should create an addin with the code in and open the template and save it it from addin initiated code.

[Sean.DiSanti]

hey, i'm kind of new here; so i'm going to go ahead and get my stupid question out of the way... what exactly is an add in, and how would i go about writing one instead of using code in a sheet or workbook?

[Bob Phillips]

An addin is a code container that loads when Excel starts up, and presents itself ready to do its stuff, but is hidden from the user. It usually creates menus or toolbars to provide an UI to the procedures within the addin.

Creating the code for an addin is no different thatn for any other Excel workbook, but the workbook is saved as a type addin. Care has to be taken to reference the correct workbook (it is very unlikely that the code would address ThisWorkbook, and Activeworkbook may not be the expected workbook), and if adding events they would have to be application events.

There are good instructions for creating one at http://www.fontstuff.com/vba/vbatut03.htm

[Sean.DiSanti]

awesome, thanks. that will come in handy i think for code i re-use a lot. sorting tabs etc.

[Bob Phillips]

Addins are really the best way to properly abstract the code logic from the data, most of my solutions will be delivered with addins.

[Sean.DiSanti]

yeah, seems like a neat concept, i've been using vba for years but am mostly self taught (on vba i mean, i've gone to school for other languages) and have just done most things in code, or with a separate executable created in another language that controls excel or outlook via COM

[Rejje]

Are you guys stealing my thread? Well, I am really glad for your commitment of solving this issue!

I've checked and I cannot use plugins for policy reasons where this project will be used. I understand the benefits of such but for now it's back to solving the problem as described above.

I feel it is best I add that allways getting the correct filename in V_50300 is not only to lock cell EXP_1010, many other things depend on it.

I have still to try the last suggestion by Sean in this topic. But I got an idea and being such a vba noob I know I can ask in here in case this is possible:

No matter the described issue in this tread I have discovered the vba-project follows the filename no matter what. Can this be used to allways get correct name of file in V_50300?

[Sean.DiSanti]

yes, just update the cell to be the filename in your Workbook_BeforeSave() event.... that way regardless of what they change it to while they've got it open, it will be right when it's saved.

[Sean.DiSanti]

and sorry didn't mean to hijack your thread; had just seen him mention add ins in a couple of places and decided to show my ignorance

[Rejje]

Lol - no problem and thanks for the engagement. It was meant as humour

When it comes to ignorance I'm waaay ahead: What exacly would the code look like and where would it be placed? (I guess in ThisWorkbook)

[Sean.DiSanti]

yeah, in your workbook code, it would just be like:
[VBA]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets(1).Range("a1").Formula = ThisWorkbook.Name
End Sub[/VBA]

[Rejje]

Almost there!

A slight modification to suite my needs made the code look like this:

[vba]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Range("V_50300").Formula = ThisWorkbook.FullName

End Sub[/vba]

When I run below sub
[vba]

Sub SaveFile()
Dim wb As Workbook
Set wb = ActiveWorkbook

If Range("V_10300") = True Then

If UCase(wb.FullName) = UCase(Range("V_50100").Value) Then
MsgBox (UCase(Range("KUNDPOST_NAMN").Value) & " skapas!")
ActiveWorkbook.SaveAs Range("V_50200").Value
Workbooks.Open wb.FullName
wb.Save
Else
wb.Save
End If

Else
MsgBox (UCase("Cannot save - name is missing!"))
'The user has to write something in EXP_1010 which will be a part of the new name for the workbook
Range("EXP_1010").Select
End If

Set wb = Nothing

End Sub

[/vba]
I don't get the correct name in cell V_50300 immediately but will have to save manually once. This is a big improvement since I won't have to close and reopen the workbook!

So, only thing now is to get above sub to save the correct file once more, I think. I have tried different methods but above sub will still not understand it is the newly created workbook named Range("V_50200").Value I want to save.

My idea is to add something like :

[vba]
If Workbook called Range("V_50200").Value is open Then

Save Workbook called Range("V_50200").Value
' which is in fact the very same workbook
[/vba]

This way we won't have to involve the problem with the active workbook and V_50300 will say the correct filename every time Sub SaveFile has run.

In case this is possible I believe this thread can come to a closure. Is it possible?