Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 21

Thread: Denying a cell to be changed in any way

  1. #1

    Denying a cell to be changed in any way

    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?

  2. #2
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Quote Originally Posted by 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?
    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]
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  3. #3
    Quote Originally Posted by Simon Lloyd
    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?

  4. #4
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    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.
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  5. #5
    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?

  6. #6
    VBAX Regular
    Joined
    Nov 2010
    Location
    Las Vegas Nv
    Posts
    74
    Location
    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]

  7. #7
    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!

  8. #8
    VBAX Regular
    Joined
    Nov 2010
    Location
    Las Vegas Nv
    Posts
    74
    Location
    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]

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    VBAX Regular
    Joined
    Nov 2010
    Location
    Las Vegas Nv
    Posts
    74
    Location
    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?

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    VBAX Regular
    Joined
    Nov 2010
    Location
    Las Vegas Nv
    Posts
    74
    Location
    awesome, thanks. that will come in handy i think for code i re-use a lot. sorting tabs etc.

  13. #13
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    Addins are really the best way to properly abstract the code logic from the data, most of my solutions will be delivered with addins.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  14. #14
    VBAX Regular
    Joined
    Nov 2010
    Location
    Las Vegas Nv
    Posts
    74
    Location
    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

  15. #15
    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?

  16. #16
    VBAX Regular
    Joined
    Nov 2010
    Location
    Las Vegas Nv
    Posts
    74
    Location
    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.

  17. #17
    VBAX Regular
    Joined
    Nov 2010
    Location
    Las Vegas Nv
    Posts
    74
    Location
    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

  18. #18
    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)

  19. #19
    VBAX Regular
    Joined
    Nov 2010
    Location
    Las Vegas Nv
    Posts
    74
    Location
    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]

  20. #20
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •