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

    Excel Form Project Questions (Excel 2000 >)

    Hi,

    I have built this custom form that inserts a Worksheet Title based on user options. It is working fine, but I do have a couple of niggles to sort out. See the attached workbook for the form.

    Questions:
    1. How do I prevent persons from typing into the combo box? They must choose a department, but must not be able to type in the box.
    2. When inserting a New Sheet, the user can type a sheet name. The code fails if they type in a name of an existing sheet. So I put in an error trap to add a suffix behind the name that the user supplies. However, the error handler fails due to the On Error Resume Next statement, (so it seems). I need help to fix this?
    3. Still on the sheet name: If I type an invalid sheet character, like an * or a ?, the code fails. How do I prevent this? (Hope this is not a big job!!)

    TX
    Attached Files Attached Files
    Regards,
    Rudi

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

    Re: Excel Form Project Questions (Excel 2000 >)

    1. Set the Style property to 2 - fmStyleDropdownList.
    2. I'd just display an error message if the user supplies an existing name.
    3. You'd have to parse the proposed name for invalid characters. You could, for example, allow only letters, digits and spaces.

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

    Re: Excel Form Project Questions (Excel 2000 >)

    TX for the answers so far, but I have more questions...

    1. My code debugs with "Cannot find project or library", and it highlights the date function? Whats wrong?
    2. I added the prefix Private to the subs in the module (so that they do not display in the macro dialog window.) But having done this I cannot call those macros from the form module. Obviously the private changes the scope of the macros to module level, but how do I get my form module to see the macros in the standard module (without the macros being listed in the macro dialog)
    3. Do I parse strings using a Find Function or InStr Function. Is the examples you created in this <post#=667,085>post 667,085</post#> similar to what I need to create in the macro to check for invalid characters in the sheetname. (Sheetname invalid characters are these 7: / * & : [ ]

    TX
    Regards,
    Rudi

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

    Re: Excel Form Project Questions (Excel 2000 >)

    1) A missing reference to an MS Project library. Select Tools | References and uncheck the missing one.

    2) You can hide a procedure from the macro list by giving it a dummy argument. E.g. change

    Sub Test()

    to

    Sub Test(Optional Dummy)

    A macro is a procedure (sub) without arguments, so by adding an argument, you effectively hide the procedure from the macro list. Because the argument is optional, you don't have to provide it - you can call the code as before.

    3) I'd loop through the characters.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Form Project Questions (Excel 2000 >)

    Instead of checking for invalid characters or for a unique name, an alternative is to save teh current name, then set the name with a "on error resume next" and see if the name of the sheet has changed. If it did proceed, if it did not, ask for a new name...

    You can essentially loop until the new name does not match the old name or loop while the sheetname equals the stored original name...

    Steve

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Form Project Questions (Excel 2000 >)

    You could use a combination of these functions:
    <pre>Function GetValidSheetName(ByVal strOrig As String) As String
    Dim varChars
    Dim varItem
    varChars = Array(":", "", "/", "?", "*", "[", "]")
    For Each varItem In varChars
    strOrig = Replace$(strOrig, varItem, "")
    Next varItem
    GetValidSheetName = Left$(strOrig, 31)
    End Function
    Function SheetExists(ByVal strSheet As String, _
    Optional ByVal wb As Workbook) As Boolean
    Dim sht As Object
    On Error Resume Next
    If wb Is Nothing Then Set wb = ActiveWorkbook
    Set sht = wb.Sheets(strSheet)
    SheetExists = Not (sht Is Nothing)
    End Function
    </pre>


    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Excel Form Project Questions (Excel 2000 >)

    Many TX Hans, Steve and Rory. You have all given me plenty to think about. I will try these solutions and see how they can rectify my code.
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Regards,
    Rudi

Posting Permissions

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