Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort sheets now working (Excel 97)

    Try the attached.
    Legare Coleman

  2. #2
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Sort sheets not working (Excel 97)

    I am so sorry to be back again.

    Hans' suggestion below worked really well. However, now a problem has arisen after setting up a new year, when adding, editing or deleting a record. The sort sheets routine does not work properly. On months that are not 31 days the colouring of cells does not work properly. From what I can see it is because part of the code refers to the last three columns AJ to AL. When it is a shorter month there are less columns and therefore AJ to AL are no longer valid.

    I could move the whole three columns over to the left following names but that still requires some significant code change.

    HELP.............I am so close now. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Kerry


    To delete superfluous dates at the end of the month:
    - Declare an extra variable at the beginning of CreateSheets:

    Dim lngDays As Long

    and add the following code in the For Next loop, after strPrevSheet = oNewSheet.Name:

    lngDays = Day(DateSerial(Year(datDate), Month(datDate) + 1, 0))
    If lngDays < 31 Then
    oNewSheet.Range(oNewSheet.Cells(1, lngDays + 4), oNewSheet.Cells(1, 34)).EntireColumn.Delete
    End If

    Regards,
    Hans

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Sort sheets not working (Excel 97)

    Almost. The shading remains on a deleted record. I have set up a small version (attached) so that I could zip it to demonstrate the problem.

    I have printed out both version of the sort sheets module and I can see you have made quite a few subtle changes, but I cant seem to figure out what would fix this problem.

    Sorry.............

    Kerry

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort sheets not working (Excel 97)

    Try this:
    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Sort sheets not working (Excel 97)

    I get a debug on deleting a staff member. It is the protect sheet. I have tried to fix it with no luck.

    Otherwise it is good.

    Kerry

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort sheets not working (Excel 97)

    I did find where I had forgotten to put in some code. Try this. If this does not fix the problem, please give me a little more to go on. What line of code gets the debug? What sheet was being worked on when the error occurred? What was the error message?
    Legare Coleman

  7. #7
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Sort sheets not working (Excel 97)

    It was still a little bit clunkly but with more luck than good management, I have managed to get it working. I have commented out a couple of things that were not necessary. There was also a missing . in

    Range(.Cells(lRow, 1), .Cells(lRow, 10)).Interior.ColorIndex = xlNone

    The only issue now seems to be that on both the userforms you can leave an entry blank. If that happens it falls over. Is there a way of making sure the user cannot leave a blank? Some sort of validation process.

    Kerry

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort sheets not working (Excel 97)

    Hi Kerry,
    This may not be the best (shortest) way of going about this, but it works. It will force all controls to contain a value!
    Insert it just after the Dim statement in both forms!

    <pre>If Me.txtFName.Value = "" Or _
    Me.txtLName.Value = "" Or _
    Me.cboLocation.Value = "" Or _
    Me.cboPosition.Value = "" Or _
    Me.cboSector.Value = "" Then
    MsgBox "Please fill out all the fields!", vbExclamation, "Missing Data!"
    Me.txtFName.SetFocus
    Me.txtFName.SelStart = 0
    Me.txtFName.SelLength = 1000
    Exit Sub
    End If
    </pre>

    Regards,
    Rudi

  9. #9
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Sort sheets not working (Excel 97)

    Thankyou Rudi. It does work.

    It's looking good. Let's hope I dont find any more problems. I think I might be wearing out my welcome.

    Kerry

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort sheets not working (Excel 97)

    Glad to hear its working.
    You might want to wait and see what Legare has in store. As I said, my solution was a quick fire! Legare may be able to shorten it or build a small loop to check for blank controls!
    Regards "Wearing out your welcome!" : I thought thats what the lounge is for!!! Interacting, sorting out problems, being taken for a learning curve.... The more questions, the better!
    Regards,
    Rudi

  11. #11
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Sort sheets not working (Excel 97)

    Hi Legare

    I added in Rudi's validation routine and it worked. Unfortunately I discovered another problem. Using the attached version try deleting all the staff and then try and start adding new staff again. If falls over. I am not sure why.

    Also I have been digging through notes I have collected over time and found something interesting on Protection. It stated the following:

    Can I set things up so my VBA macro can make changes to Locked cells on a protected sheet? Yes, you can write a macro that protects the worksheet, but still allows changes via macro code. The trick is to protect the sheet with the UserInterfaceOnly parameter. Here's an example:

    ActiveSheet.Protect UserInterfaceOnly:=True
    After this statement is executed, the worksheet is protected -- but your VBA code will still be able to make changes to locked cells and perform other operation that are not possible on a protected worksheet.

    I wondered if this might be suitable for my spreadhseet?

    Hope you can help.

    Kerry

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort sheets not working (Excel 97)

    This should fix that:

    I can't answer the question on protecting the userinterfaceonly. That completely depends on what you want the protection to do.
    Legare Coleman

  13. #13
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Sort sheets not working (Excel 97)

    Legare I am sorry but although I can now delete all staff and edit I cannot add new!

    I really really appreciate your help, but I wonder if you might be able to tell me what you do to fix things and then I learn a bit more.

    Kerry

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort sheets not working (Excel 97)

    OK, here is another try:
    Legare Coleman

  15. #15
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Sort sheets not working (Excel 97)

    Thanks Legare - I think this looks like a goer.

    Please tell me though what was wrong??? I need to know so I can learn.

Page 1 of 2 12 LastLast

Posting Permissions

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