Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel questions (2003)

    Hi,
    I have a few excel questions I'm hoping someone can answer.

    1) The computer that I'm working on doesn't have Calendar Control 10.0 as a reference, is there anyway to create a calendar without this? I'm hoping to populate a cell/s with a date from this calendar.

    2) I have a combobox that references to a hidden work sheet. Is it possible to default the value of this to a blank when the workbook is opened? I also don't really want people to be able to save the workbook as this will alter its data. Ideally not using Read-Only.

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

    Re: Excel questions (2003)

    Concerning the calendar Q. There are ready made calendars that can be downloadsed from the internet for use in Excel. Try This page for example
    Hope its useful!
    Regards,
    Rudi

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel questions (2003)

    1) You can download the mscal.ocx control from I Don't Have a Copy of mscal.ocx.

    2a) You can use the Workbook_Open event to clear the value of the combo box.

    2b) You could save the workbook as a template (.xlt) and make it available read-only. The users can then create a new workbook from it, if they save that, the template will remain unchanged.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel questions (2003)

    1) Can I install this locally in relation to the specific document? Unfortunately I have no access to our C drive as our computers are networked......i don't think they trust us!
    2)
    a)Would something like this work?
    Private Sub Workbook_Open()
    Me.ComboBox1 = Null
    Me.ComboBox10 = Null
    Me.ComboBox2 = Null
    Me.ComboBox3 = Null
    Me.ComboBox4 = Null
    Me.ComboBox5 = Null
    Me.ComboBox6 = Null
    Me.ComboBox7 = Null
    Me.ComboBox8 = Null
    Me.ComboBox9 = Null
    End If
    End Sub
    [img]/forums/images/smilies/cool.gif[/img] Yep this makes sense, but is there a way to make the user not save on the exit event of the workbook, without their knowledge.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel questions (2003)

    1) In that case, you're better off with one of the calendars built from "native" controls. See for example Orlando's VBA and Excel Site (Site de VBA e Excel do Orlando) (free download)

    2a) What does the End If do in there? Otherwise, it looks OK.

    2b) You could cancel the save in the Workbook_BeforeClose and Workbook_BeforeSave events. The code MUST go into the ThisWorkbook module:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Me.Saved = True
    End Sub

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    End Sub

    Note:
    If you just copy the code into ThisWorkbook, you won't be able to save the workbook any more, because that's what you wanted! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    To avoid this problem, open the workbook with macros disabled, then copy the code into ThisWorkbook, and save the workbook.
    Next time it is opened with macros enabled, it cannot be saved.
    Of course, this means that if another user opens the workbook with macros disabled (for example because macro security is set to High), (s)he can save the workbook!

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel questions (2003)

    Thanks, that works a treat.
    You're advice, as always, is spot on!!! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

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