Results 1 to 28 of 28

Thread: Stop user editing cells - Excel

  1. #1

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

    Stop user editing cells - Excel

    I am trying to stop the user from being able to edit some of the cells in the worksheet, such as column titles etc.

    I have tried the Locked property, but this doesn't seem to work as I can still change the titles.
    I have looked through the help files and the properties, but I can't seem to find one that works, or maybe I am not calling them correctly, althought those that I have tried I copied from the VB help files!
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  2. #2
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Stop user editing cells - Excel

    Hi

    You need to protect the sheet after locking the cells...

    Hope this helps...
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  3. #3

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

    Re: Stop user editing cells - Excel

    I have this code here and in the Protect worksheet I have put a tick in the unlocked cells, but the whole sheet seems to be locked?

    VB Code:
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. Range("A1:AI7").Locked = True
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  4. #4
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Stop user editing cells - Excel

    By default the property of all cells in a worksheet are set to Locked=true.
    If you don't want specific cells to be locked then set their locked property to false

    for example

    VB Code:
    1. 'assuming this is the range you want to unlock
    2.     Range("A1:F1").Select
    3.     Selection.Locked = False
    4.     Selection.FormulaHidden = False
    5.     ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

    or if you don't want just the column headings to be changed then select all cells in the worksheet. Set the entire cells locked property to false and then change the property of the relevant cells to true

    for example...
    VB Code:
    1. 'entire worksheet
    2.     Cells.Select
    3.     Selection.Locked = True
    4.     Selection.FormulaHidden = False
    5.  
    6.     'assuming this is the range you want to unlock
    7.     Range("A1:E1").Select
    8.     Selection.Locked = False
    9.     Selection.FormulaHidden = False
    10.     ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

    Hope this helps...
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  5. #5
    Addicted Member
    Join Date
    Jan 2007
    Location
    England
    Posts
    234

    Re: Stop user editing cells - Excel

    you need to protect the sheet for this to work

  6. #6
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Stop user editing cells - Excel

    you need to protect the sheet for this to work
    @Mitch: Refer to post#2
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  7. #7

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

    Re: Stop user editing cells - Excel

    In the sheets properties dialog I have changed
    Code:
    EnableSelection TO xlUnlockedCells
    Now to stop the cells in the first 7 rows to be edited, I would use this code is that correct, and would I put it in the sheets 'Activate' sub?
    VB Code:
    1. 'entire worksheet
    2.     Range("A1:AI7").Select
    3.     Selection.Locked = True
    4.     Selection.FormulaHidden = False
    My command button is in "A1" - Which consists of "A1:A6" merged cells.

    Locking this, would it stop the user being able to select the button, or should I just change the Range in the code to
    VB Code:
    1. Range("B2:AI7").select
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  8. #8
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Stop user editing cells - Excel

    Locking this, would it stop the user being able to select the button,
    locking/unlocking cells don't effect the properties of Commandbutton.

    you need to right click commandbutton and set it's locked property to "true" in case you don't want user's to select it... once it is done, protect the sheet to see the effect...
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  9. #9

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

    Re: Stop user editing cells - Excel

    Hey koolsid, thanks again, but I think I am missing something here.

    If I added my worksheet to an attachment would you take a look at it for me just to point me in the right direction.
    I am not asking you to do anything to it, just let me know where I am going wrong.
    Last edited by aikidokid; Feb 4th, 2007 at 10:00 AM.
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  10. #10
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Stop user editing cells - Excel

    Definitely...
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  11. #11

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

    Re: Stop user editing cells - Excel

    What I am rtying to recreate is a matrix style sheet that I can use to see which drivers have been either fully trained or still need training.

    If the drivers records are complete then the drivers name is in white on a black background. (This could be changed - I just want it to stand out to make it easier to see who is not fully trained)
    If a record is incomplete then the empty cell should be coloured Yellow. (Don't ask me why, that's just what they want)
    If when I manually enter onto the sheet some data, the yellow cell should become white, provided the data meets the requirement.
    Then to check if all cells in that row are full, with the exception of the last cell which is optional, make the first cell in the row as above. (White text on Black back colour)

    I have a lot of this, but when the sheet is protected, all sorts of errors seem to occur.

    There is no password to protect the sheet. I have ticked the allow insert rows and allow autofilter options. I am not quite sure to do with the cells to allow as I am getting mixed up on that one.
    I want to let them alter any cell after the 7th row.

    By the way, I have removed the code to close all the forms as this is still looping when it shouldn't .

    P.S. Is there a way to stop the first column being selected when the user selects the command button?
    Last edited by aikidokid; Feb 8th, 2007 at 09:16 AM.
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  12. #12
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Stop user editing cells - Excel

    P.S. Is there a way to stop the first column being selected when the user selects the command button?
    I want to let them alter any cell after the 7th row.
    this is not possible... cause the very first cell in the Eight Row i.e. A8 will be from the 1st column...

    If you want that the command button can be selected and any cell in the range A8:IV65536 can be selected then that is possible.

    If the drivers records are complete then the drivers name is in white on a black background. (This could be changed - I just want it to stand out to make it easier to see who is not fully trained)
    If a record is incomplete then the empty cell should be coloured Yellow. (Don't ask me why, that's just what they want)
    If when I manually enter onto the sheet some data, the yellow cell should become white, provided the data meets the requirement.
    Then to check if all cells in that row are full, with the exception of the last cell which is optional, make the first cell in the row as above.
    For all this you don't need to write a code. you can achieve the same by conditional formating...

    I could see a lot of forms and lots of coding..... need some time to understand the same....
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  13. #13

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

    Re: Stop user editing cells - Excel

    For all this you don't need to write a code. you can achieve the same by conditional formating...
    I wish I had worked this out about a week or two ago

    Is this what you mean to stop the first column being selected in the cmdAdd_Click :
    VB Code:
    1. 'stops the first column from being selected every time the cmdButton is selected,
    2. Range("GG600 ").Select
    Cos if it is, I got this wrong as well.
    I could see a lot of forms and lots of coding..... need some time to understand the same....
    you're a brave person to even try to understand what I have written - I struggle most of the time

    I thought VB was hard enough, trying to move to VBA where a lot of VB things don't apply is, well ......
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  14. #14
    Addicted Member
    Join Date
    Jan 2007
    Location
    England
    Posts
    234

    Re: Stop user editing cells - Excel

    im findin it hard goin the other way lol
    vba - vb

  15. #15

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

    Re: Stop user editing cells - Excel

    Quote Originally Posted by Mitch_s_s
    im findin it hard goin the other way lol
    vba - vb
    I'm lookin hard in the small print to see where it says it's all easy to do any of this.
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  16. #16
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Stop user editing cells - Excel

    Whoa!!!

    There are many errors

    Okay, what is your deadline for this project? (this is the 1st question that comes to my mind...)
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  17. #17

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

    Re: Stop user editing cells - Excel

    Quote Originally Posted by koolsid
    Whoa!!!

    There are many errors

    Okay, what is your deadline for this project? (this is the 1st question that comes to my mind...)
    I probably have until the end of March I expect, But I could probably get an extention as I am doing it as a favour.
    They also know I am not a professional programmer so there would be a fair bit of tolerance with it - I'm hoping.

    What do you mean you can tell I'm not a professional programmer .....
    HOW!
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  18. #18
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Stop user editing cells - Excel

    What do you mean you can tell I'm not a professional programmer .....
    HOW!
    I never said that...

    I am still working on the file. I am sorry but I think we need to remake the file...

    If you are game(I am game) then lets start from the beginning...

    What are the exact requirements? If you can list it out pointwise, it'll help me understand much more clearly...
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  19. #19

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

    Re: Stop user editing cells - Excel

    I have PM'ed you and I will post a newer Excel sheet once I think I have got close to the conditional formatting I am after, or when I finally give up
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  20. #20
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Stop user editing cells - Excel

    ok
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  21. #21

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

    Re: Stop user editing cells - Excel

    Ok give up ... well almost

    I have one of the conditions sorted.

    Before I do post what I have, can you give me a clue on how to tell whcih row the user has been editing, and also some way to find out how many columns there are used in my sheet.
    I can get the total columns in excel, but not just the ones I have used.

    (I don't want to hard code this as in the future, although distant it may be, there may be new training items to be added in a new column)
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  22. #22
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Stop user editing cells - Excel

    I can get the total columns in excel, but not just the ones I have used.
    let's say the values in
    cell A1 = "john"
    cell B1 = "andy"
    cell C1 = "koolsid"
    cell D1 = "vbforums"
    cell E1 = "office"

    This will give you the column number of the last cell in the used range...

    VB Code:
    1. Private Sub CommandButton1_Click()
    2.  
    3. MsgBox Range("A1").SpecialCells(xlCellTypeLastCell).Column
    4.  
    5. End Sub

    Hope this helps...
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  23. #23

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

    Re: Stop user editing cells - Excel

    Thanks koolsid

    I am getting there

    One more question and I may have got what I am looking for - well this bit anyway

    If I have the row number stored in an Integer called "RowNum" and I now have the last column used stored in a string "LastCol" how do I join then up to give me something like ?

    VB Code:
    1. Range(LastCol & RowNum)

    Just seem something about cStr so going to take a look into this in the mean time.
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  24. #24

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

    Re: Stop user editing cells - Excel

    Hmm...

    I am using this
    LastCol = Range(OldCell.Address).SpecialCells(xlCellTypeLastCell).Column
    to store the last column letters (Name) in the row that is used and has data in it.

    Firstly, is this correct?
    Secondly, if it is, why when I have 35 columns does it return '70'

    So this way I wouldn't need the Column Letter, e.g AH, just the number of columns to loop through.
    To get this just add the column number from the active cell to the above total.

    Or maybe, just maybe, I misunderstood what your last post meant.
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  25. #25
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Stop user editing cells - Excel

    If I have the row number stored in an Integer called "RowNum" and I now have the last column used stored in a string "LastCol" how do I join then up to give me something like ?

    Range(LastCol & RowNum)
    Does this help?

    VB Code:
    1. Private Sub CommandButton1_Click()
    2.  
    3.     aaa = "A"
    4.     bbb = 1
    5.    
    6.     'if you want the address then
    7.     MsgBox Range(aaa & bbb).Address
    8.    
    9.     'if you want the value then
    10.     MsgBox Range(aaa & bbb).Value
    11.    
    12.     'and so on...
    13.    
    14. End Sub
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  26. #26
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Stop user editing cells - Excel

    LastCol = Range(OldCell.Address).SpecialCells(xlCellTypeLastCell).Column
    what is oldcell.address?
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  27. #27

    Thread Starter
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

    Re: Stop user editing cells - Excel

    oldcell.Address is the location of the cell that has been edited, before it loses focus to the next cell.

    I found this on this forum

    It is declared as OldCell As Range
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  28. #28
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Stop user editing cells - Excel

    Hi

    Have you gone thru Si's tutorial on Excel?

    http://vbforums.com/showthread.php?t=391665

    see post no-8 in the above tutorial
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

Posting Permissions

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



Click Here to Expand Forum to Full Width