Consulting

Page 4 of 4 FirstFirst ... 2 3 4
Results 61 to 76 of 76

Thread: Solved: Invoking the excel saveas screen

  1. #61
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    saved file

  2. #62
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    Pico,
    OK, so your first issue is where the sheets are pasted. The reason they are getting pasted out of order is that you are copying the entire workbook for the job_spec_sheet, and you have assigned the worksheets prior to that?? If you want my help you need to use the code that I've modified. If you look at the last post I gave you, it's coded the correct way. The way yours is coded you end up trying to remove code from a sheet without code in, and just ignore the sheet with the code.

    Second issue, is the update link that keeps appearing. I've reviewed the workbook you attached, and the reason you are getting this, is because you have checkboxes with linked cells still attached to some sheet called
    release.xls. Do these link cells need to be there? If not, remove them from the master copy of your workbook. To be sure they are all removed, just goto the excel menu
    edit.....links
    if they are all gone the links option should be grayed out.

    Third,
    Is there a reason why you can't upload the workbook that you are producing the sheet with? I'm trying to determine what's going on by looking at your output. This would be much easier to troubleshoot with the actual workbook that is used to produce the output sheet. That way, I could make the necessary corrections, and post it back to the thread.

    Cal
    The most difficult errors to resolve are the one's you know you didn't make.


  3. #63
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    Cal,
    I cant upload my original workbook because the filesize is around 2MB. This forum supports around 300kb?. I do need the links in the original workbook. I use them to calculate the partnumbers in another sheet. Is there a way of copying the checkboxes without the links attached?. Please rememeber that i do need them in my original. The code that you supplied at the moment did not work like i wanted it. For example it created 2 parts sheets for me in the saved file.I will try to look at the code that you have supplied me and try to work around it. Btw, i thank you for all the help you have given me so far.

  4. #64
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    pico, can you zip up the file and reduce its size enough to be able to upload it?
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  5. #65
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    pico,
    I will PM you my hotmail account address, send it there. This will make the process much easier.

    Cal
    The most difficult errors to resolve are the one's you know you didn't make.


  6. #66
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Hang in the Cal....your doing a heck of a job....seriously
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  7. #67
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    I have emailed the file to Cal.

  8. #68
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    I was having problems sending it through gmail. Sent it through hotmail instead.

  9. #69
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    This is an automatically generated Delivery Status Notification.Delivery to the following recipients failed. Ithere something wrong with your email Address>??? HOtmail did this as well

  10. #70
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    sorrry didnt mean to post your email here. DOH

  11. #71
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    No worries,
    Not like I'm going get any extra junk mail in my hotmail account.

    OK,
    I've got your workbook, and I've figured out where the code is.
    I've resolved the copy issue on ps2, and removed the formula's from the final sheet. My copy is getting some NA errors, but I'm assuming that's because I'm bypass the normal population method to run it. I've also removed some blank events from the job_spec_sheet that were causing the enable macro's message to appear, which eliminates the need to erase the methods programmatically.

    NEXT PROBLEM TO RESOLVE

    That the last problem is the request to update links. These are appearing because you have checkboxes linked to your other workbooks(In the release.xls workbook) using the linkedcell property for the checkboxes.

    Are those links needed? If they are needed, then I will need to do some research to figure out how to copy the checkboxes without the linked cell property, and keep the current state of the checkbox.

    Let me know.

    Cal
    The most difficult errors to resolve are the one's you know you didn't make.


  12. #72
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    Yes, I did mention in my previous post that the links were needed in the original sheet. There are being used to calculate the partnumbers in the second sheet of the orginal workboook

  13. #73
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    So in the copied sheet we need to have the checkbox state, but not the link? and we need to leave the original as it is. OK, let me look into that.

    Cal
    The most difficult errors to resolve are the one's you know you didn't make.


  14. #74
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    pico,
    I think we are finally there. I was able to remove the linkedCell property without changing the state of the checkbox. I was then able to open the final produced workbook without the enable macro's and without the update links.
    I've sent the zip file to your hotmail account. Let me know if this takes care of the problems.

    Cal

    PS-A very well put together wizard by the way.
    The most difficult errors to resolve are the one's you know you didn't make.


  15. #75
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    Thank you Cal. Exactly what i needed. Finally i can say iam done with this project. I appreciate your help very much. Btw iam glad you like the design. I was going for a professional look. In the end i think it came out pretty good. Not too bad for a first timer with VBA. Thanks again. Cheers . HAHA..i can finally close this message.

  16. #76
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    Pico,
    Glad everythings working now. You were looking for some out of the ordinary stuff, which is why it took so long to come up with a solution.

    Thought I would post the finalized code, just in case anyone else has to deal with something similiar.

    [vba]
    Dim wb As Workbook, ws1 As Worksheet, ws2 As Worksheet
    Dim pb As Workbook, ps1 As Worksheet, ps2 As Worksheet
    Dim StartLine As Long
    Dim HowManyLines As Long
    Dim response As String
    Dim s As OLEObject

    'Set references to the Master workbook
    Set wb = ActiveWorkbook
    Set ws1 = wb.Sheets("JOB_SPEC_FORM")
    Set ws2 = wb.Sheets("PARTS1")
    'Add new workbook
    Set pb = Workbooks.Add
    Application.ScreenUpdating = False
    'Unhide job_spec_form
    Sheet1.Visible = xlSheetVisible
    'Copy data from first sheet, set reference to it and hide original workbook again.
    ws1.Copy pb.Sheets(1)
    Set ps1 = ActiveSheet
    Sheet1.Visible = xlSheetHidden
    'cycle through shapes and remove link
    For Each s In ps1.OLEObjects
    If Left(s.Name, 6) = "CheckB" Then
    s.LinkedCell = ""
    End If
    Next s
    'Copy data from second sheet, set references to it and copy and paste values
    ws2.Visible = xlSheetVisible
    ws2.Copy , pb.Sheets(1)
    Set ps2 = ActiveSheet
    ws2.Cells.Copy
    ps2.Range("A1").PasteSpecial (xlPasteValues)
    ws2.Visible = xlSheetVeryHidden
    ActiveWindow.DisplayGridlines = False
    Application.ScreenUpdating = True
    'response = "False"
    ' Do Until response <> "False"
    ' response = Application.Dialogs(xlDialogSaveAs).Show
    ' Loop
    Application.Dialogs(xlDialogSaveAs).Show
    pb.Close False
    [/vba]
    The most difficult errors to resolve are the one's you know you didn't make.


Posting Permissions

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