+ Reply to Thread
Results 1 to 11 of 11

Length of string, not value related to string appearing in input box prompt.

  1. 10-23-2014, 09:49 AM #1
    ColdDay
    ColdDay is offline
    Registered User
    Join Date
    09-29-2014
    Location
    KC
    MS-Off Ver
    2007
    Posts
    20

    Length of string, not value related to string appearing in input box prompt.

    I have a long procedure but have posted only the code that I feel is the problem. I will post the complete procedure if needed. I have comments included in the code section. I hope that it is OK to do that.

    I am entering time sheets using input boxes to gather info, but the Prompt part of the boxes also display what has already been entered. Such as ID # and name, which work OK. I also want the JobStepNumber to display (which it does), plus the JobStepName (description of the step) to display.

    The JobStepNumber appears in the input boxes as desired. It is the JobStepName (description of the step) related to the JobStepNumber that is not working when the length of the JobStepNumber is 9. Less than 9 is OK.

    Please Login or Register  to view this content.
    Any help would be greatly appreciated. Thanks.

    Bill
    Last edited by ColdDay; 10-23-2014 at 10:00 AM. Reason: Code may not be the problem. Some #'s are working. Will test more and get back.
    Reply With QuoteRegister To Reply
  2. 10-23-2014, 10:29 AM #2
    ColdDay
    ColdDay is offline
    Registered User
    Join Date
    09-29-2014
    Location
    KC
    MS-Off Ver
    2007
    Posts
    20

    Re: Length of string, not value related to string appearing in input box prompt.

    Have discovered that some 9 digit numbers work and others do not.

    Thoughts?
    Reply With QuoteRegister To Reply
  3. 10-23-2014, 10:31 AM #3
    LJMetzger
    LJMetzger is offline
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Length of string, not value related to string appearing in input box prompt.

    Hi Bill,

    The following constructions should help you get out of your quandary.
    Please Login or Register  to view this content.
    Variable typing is very important. The .find() function returns a 'Range type' which was probably the cause for most of your problems. You are 'cross typing' other things in the code (e.g. trying to find the length of a double variable, and extracting substrings from a double variable), which is probably not good programming practice, but Excel lets you get away with it, so your code will probably work when you fix the range problem.

    Lewis
    Reply With QuoteRegister To Reply
  4. 10-23-2014, 10:31 AM #4
    Winon
    Winon is offline
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Length of string, not value related to string appearing in input box prompt.

    Hello ColdDay,

    Right at the bottom of your code snippet, you have;

    Please Login or Register  to view this content.
    Try it with;

    Please Login or Register  to view this content.
    Hope that helps.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
    Reply With QuoteRegister To Reply
  5. 10-23-2014, 10:42 AM #5
    ColdDay
    ColdDay is offline
    Registered User
    Join Date
    09-29-2014
    Location
    KC
    MS-Off Ver
    2007
    Posts
    20

    Re: Length of string, not value related to string appearing in input box prompt.

    I'll work on both and will get back as soon as possible. Thanks for the input!
    Reply With QuoteRegister To Reply
  6. 10-23-2014, 11:34 AM #6
    ColdDay
    ColdDay is offline
    Registered User
    Join Date
    09-29-2014
    Location
    KC
    MS-Off Ver
    2007
    Posts
    20

    Re: Length of string, not value related to string appearing in input box prompt.

    Lewis, This is what I am using now. Following the code is the results for the different lengths of JobStepNumber.

    Please Login or Register  to view this content.
    Len of 3 (job 186) gives the correct description, but is formatted as 18-6-86. S/b 1-86.

    Len of 7 (job 9099997) gives description, formatted as 90-9999-97. S/b 9-0999-97.

    Len of 8 (12508514) gives description, formatted 12-5085-14. Both OK.

    Len of 9 (146314951) gives "9", formatted as 14-6314-51. No description and s/b 146-3149-51.

    I'm still missing something.

    Bill
    Reply With QuoteRegister To Reply
  7. 10-23-2014, 11:38 AM #7
    ColdDay
    ColdDay is offline
    Registered User
    Join Date
    09-29-2014
    Location
    KC
    MS-Off Ver
    2007
    Posts
    20

    Re: Length of string, not value related to string appearing in input box prompt.

    Winon, I'm using 3 b/c the user can also enter an H to go to a MsgBox for help as well as entering a number for the hours. Though I may not completely understand how to use the type #'s.
    Reply With QuoteRegister To Reply
  8. 10-23-2014, 12:04 PM #8
    ColdDay
    ColdDay is offline
    Registered User
    Join Date
    09-29-2014
    Location
    KC
    MS-Off Ver
    2007
    Posts
    20

    Re: Length of string, not value related to string appearing in input box prompt.

    Making changes in red below make formatting work, but still get a 9 when length of string is 9. Other lengths work as desired.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 10-23-2014, 03:13 PM #9
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Length of string, not value related to string appearing in input box prompt.

    Hello ColdDay,

    I think you would be ahead of the game by using a UserForm to collect your user inputs. You would have a lot more control and fewer problems.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
  10. 10-23-2014, 03:48 PM #10
    LJMetzger
    LJMetzger is offline
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Length of string, not value related to string appearing in input box prompt.

    Len of 3 (job 186) gives the correct description, but is formatted as 18-6-86. S/b 1-86.

    Len of 7 (job 9099997) gives description, formatted as 90-9999-97. S/b 9-0999-97.

    Len of 8 (12508514) gives description, formatted 12-5085-14. Both OK.

    Len of 9 (146314951) gives "9", formatted as 14-6314-51. No description and s/b 146-3149-51.
    When testing the above data values with your code, I get all the should be values. It seems to me that you format the values correctly. To evaluate your description problem, you will have to upload a sample workbook.

    Lewis
    Reply With QuoteRegister To Reply
  11. 10-23-2014, 05:32 PM #11
    ColdDay
    ColdDay is offline
    Registered User
    Join Date
    09-29-2014
    Location
    KC
    MS-Off Ver
    2007
    Posts
    20

    Re: Length of string, not value related to string appearing in input box prompt.

    Got to work on Monday and the code is working as desired.
    Last edited by ColdDay; 10-27-2014 at 01:05 PM. Reason: Code on Monday morning is working.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Shorten string length if exceeds 31 characters (max sheet name length)
    By r2fro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2014, 05:57 AM
  2. [SOLVED] Take date from string and input into varying column length
    By bruizer31 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-26-2013, 10:55 AM
  3. Max string length of Shape.Fill.UserPicture(PictureImage as String)?
    By AlvaroSiza in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-13-2013, 09:36 AM
  4. [SOLVED] Translate form data into a text string and output that string into a preformated length
    By TJ Saulnier in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-19-2012, 03:58 PM
  5. [SOLVED] Want to input one character to cause related string to display in.
    By Peace of Christ be with you in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-20-2005, 03:06 AM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1