Results 1 to 10 of 10

Thread: Any Excel experts?

  1. #1
    Hardcore Til I Die htid's Avatar
    Join Date
    Mar 2006
    Location
    London
    Posts
    1,802
    Thanks
    81
    Thanked
    32 times in 22 posts
    • htid's system
      • Motherboard:
      • Asus P5B
      • CPU:
      • C2D E6600 @ 3ghz
      • Memory:
      • 2gb Corsair XMS2
      • Storage:
      • 180gb Spinpoint
      • Graphics card(s):
      • XFX 7900GTX
      • PSU:
      • 500W Seasonic S12
      • Case:
      • Akasa Mirage
      • Monitor(s):
      • 18" Dell

    Any Excel experts?

    Our finance director has it in her head that there's some way in Excel that you can put data in a cell, then once it's in, when you save the file the cell generates the next number in the sequence.

    To make it clearer, we want to use Purchase Orders in Excel. We obviously have PO numbers which are in sequence, so she wants to make the spreadsheet open to everyone, with a PO number in a certain cell. Once someone enters the details for a PO, then save the document, the next PO number is automatically generated in the same box as the previous, with nobody being able to edit the previous PO. She uses the term "sealed cells" for it but she's not sure if it's correct.

    Does that make sense? Does anybody know anything about it? Even knowing the name of the function would be a start as I don't know what to search for to find tutorials.

    Thanks for any help!

  2. #2
    cat /dev/null streetster's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    4,138
    Thanks
    119
    Thanked
    100 times in 82 posts
    • streetster's system
      • Motherboard:
      • Asus P7P55D-E
      • CPU:
      • Intel i5 750 2.67 @ 4.0Ghz
      • Memory:
      • 4GB Corsair XMS DDR3
      • Storage:
      • 2x1TB Drives [RAID0]
      • Graphics card(s):
      • 2xSapphire HD 4870 512MB CrossFireX
      • PSU:
      • Corsair HX520W
      • Case:
      • Coolermaster Black Widow
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • DELL U2311
      • Internet:
      • Virgin 50Mb

    Re: Any Excel experts?

    how about adding some VBA behind a 'save' button that saves the document, increases the value in the PO number cell, and clears the rest of the document?


  3. #3
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: Any Excel experts?

    Affriad you can only do this with some (easy) vba afik.

    First of Name the PO number Cell range.

    Then 'protect' it so users can't edit it by accident. (tools->protection->allow user to edit ranges and cells)

    Then put some simple code in the Workbook, on the BeforeSave event, to simply just increment the number.
    throw new ArgumentException (String, String, Exception)

  4. #4
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: Any Excel experts?

    Quote Originally Posted by TheAnimus View Post
    Affriad you can only do this with some (easy) vba afik.

    First of Name the PO number Cell range.

    Then 'protect' it so users can't edit it by accident. (tools->protection->allow user to edit ranges and cells)

    Then put some simple code in the Workbook, on the BeforeSave event, to simply just increment the number.

    Out of interest, how would you get around the fact that VBA can't edit the protected cell? I can figure out how to get a macro to change from a protected to unprotected state, but it asks for the password, which isn't ideal. I'm curious because I enjoy playing with Excel and last time I did something like this, I didn't have to worry about people editing the sheet (as it did pallet labels)

    (\___/) (\___/) (\___/) (\___/) (\___/) (\___/) (\___/)
    (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=)
    (")_(") (")_(") (")_(") (")_(") (")_(") (")_(") (")_(")


    This is bunny and friends. He is fed up waiting for everyone to help him out, and decided to help himself instead!

  5. #5
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: Any Excel experts?

    ActiveSheet.Unprotect

    takes a 'password'

    sometimes i think the word password is too safe, we need a phrase like

    AlmostObscuredWord.
    throw new ArgumentException (String, String, Exception)

  6. #6
    Registered+
    Join Date
    Dec 2007
    Posts
    45
    Thanks
    0
    Thanked
    0 times in 0 posts

    Re: Any Excel experts?

    Excel's not the best for this though - couldn't the user exit without saving? Wouldn't it be better to do it in Access and build a user form?

  7. #7
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: Any Excel experts?

    Quote Originally Posted by TheAnimus View Post
    ActiveSheet.Unprotect

    takes a 'password'

    sometimes i think the word password is too safe, we need a phrase like

    AlmostObscuredWord.
    I tried that, but it pops up with a password box.

    Does something like ActiveSheet.Unprotect(password) work then?

    (\___/) (\___/) (\___/) (\___/) (\___/) (\___/) (\___/)
    (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=)
    (")_(") (")_(") (")_(") (")_(") (")_(") (")_(") (")_(")


    This is bunny and friends. He is fed up waiting for everyone to help him out, and decided to help himself instead!

  8. #8
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: Any Excel experts?

    password:= password.

    hard coded, its nasty
    throw new ArgumentException (String, String, Exception)

  9. #9
    SiM
    SiM is offline
    Senior Member
    Join Date
    Apr 2006
    Location
    London
    Posts
    7,787
    Thanks
    300
    Thanked
    633 times in 422 posts
    • SiM's system
      • Motherboard:
      • P5K Premium
      • CPU:
      • Q6600
      • Memory:
      • 8GB PC2-6400 OCZ ReaperX + Platinum
      • Storage:
      • 3 x 320gb HD322HJ single platter in Raid 0
      • Graphics card(s):
      • PNY GTX285
      • PSU:
      • Corsair TX650W
      • Case:
      • Antec 1200
      • Monitor(s):
      • 2407-HC

    Re: Any Excel experts?

    you are going to have to password the vba modules too then

  10. #10
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: Any Excel experts?

    Quote Originally Posted by TheAnimus View Post
    sometimes i think the word password is too safe, we need a phrase like

    AlmostObscuredWord.
    well.
    throw new ArgumentException (String, String, Exception)

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel help (Conditional Formatting)
    By Workaholic in forum Software
    Replies: 3
    Last Post: 04-02-2008, 01:31 AM
  2. .NET Reading Multiple Excel Files
    By Shadow_101 in forum Software
    Replies: 5
    Last Post: 23-11-2007, 03:22 PM
  3. Excel + Database + Automated
    By Kezzer in forum Software
    Replies: 4
    Last Post: 29-03-2007, 04:54 PM
  4. Excel windows
    By Steve in forum Software
    Replies: 4
    Last Post: 25-07-2006, 12:44 PM
  5. Replies: 6
    Last Post: 12-08-2005, 08:50 AM

Posting Permissions

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