+ Reply to Thread
Results 1 to 5 of 5

Textbox showing random unwanted entry number

  1. #1
    Forum Contributor
    Join Date
    08-04-2008
    Location
    West Calder, Scotland
    MS-Off Ver
    365
    Posts
    372

    Textbox showing random unwanted entry number

    Good evening folk

    This one has me completely stumped,,, hopefully someone can help........

    Userform is used to input new staff member... no issues

    When the staff information has been entered the amount of training required is automatically allocated and an entry number allocated for each training, this entry number comes from the maximum already in the database. +1 etc.... i.e.,, if the current high number in database is 125 and if the job for the new staff requires 6 items of training then the first item number will be 126,, second 127 etc.

    All the information is then copied to the bottom of the database

    To update the training,,,, second Userform is opened,,,, select the staff member and all their current training is shown in a list box

    Up to this Point there is no issues......... all the information is correct..........

    To update the training,,, I double click on the required information and its moved to a range of textboxes and combo boxes.... this is where it is randomly going wrong

    Sometimes the 'entry number is wrong,,,, it is not always he same entry,,, it is random,,, but when one is wrong,,, then the others that are wrong match the same number.....

    e.g..... entry 2 when double clicked will the show as entry 25 in the textbox,,, entry number 5 will also show 25 and of course entry 25..... shows 25,,, however all the other textboxes and empty....

    Next time I try it,,, it might be entry number 1 showing 135,,, entry 11 shows 135 etc. and entry 2, 5 and 25 are showing correctly

    All the information on the spreadsheet is correct, the information in the list box is correct,, I have ensured the range is formatted to txt,,, but not matter how it is formatted,,, itís the same....

    Many thanks

    JT


    Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    'declare the variables
    Dim ID As String
    Dim i As Integer
    Dim findvalue

    framerecords.Visible = False: frameupdate.Visible = True

    'get the select value from the listbox
    For i = 0 To lstLookup.ListCount - 1
    If lstLookup.Selected(i) = True Then
    'set the listbox column
    ID = lstLookup.List(i, 9)
    End If
    Next i
    'finds the value in the range
    Set findvalue = Sheet2.Range("L:L").Find(What:=ID, LookIn:=xlValues).Offset(0, -9)

    'adds the values to the userform boxes
    cNum = 10
    For X = 1 To cNum
    Me.Controls("update" & X).Value = findvalue.Offset(, X - 1).Text

    Next
    'disable the controls to make the user select an option

    'error block
    On Error GoTo 0
    Exit Sub

    End Sub
    Last edited by JamesT1; 01-16-2017 at 04:06 PM. Reason: additional information

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Textbox showing random unwanted entry number

    Hi,

    If the question is about some anomaly that occurs during the interactivity of some controls on a user form then we really do need to see the workbook and macros in order to be able to comment with any certainty.

    Upload the workbook and give us some examples of the selection choices you make, what results you get and what results you expect
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    08-04-2008
    Location
    West Calder, Scotland
    MS-Off Ver
    365
    Posts
    372

    Re: Textbox showing random unwanted entry number

    Hi Richard

    On this sheet,,,,

    select records,,, type in surname 'Thomson'.... then 'Lookup Information'

    When you double click an entry,,, the list box for entry should show the entry number you selected

    In the list box double click entry 2.... in the text box for entry,,, the entry is 25.... double click entry 5 and its the same
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    08-04-2008
    Location
    West Calder, Scotland
    MS-Off Ver
    365
    Posts
    372

    Re: Textbox showing random unwanted entry number

    Hi guys

    some further information,,, if there are 25 entries in the range the number 2 is giving 25,,, the number 5 is giving 25..

    if there are 36 entries in the range,,, 2 and 5 are reading as they should,,, 3 and 6 are returing 36 ....... 48 entries,,, 4 and 8 are returning 48.... 3 and 6 are ok etc

  5. #5
    Forum Contributor
    Join Date
    08-04-2008
    Location
    West Calder, Scotland
    MS-Off Ver
    365
    Posts
    372

    Re: Textbox showing random unwanted entry number

    Hi folk

    I now resolved this,,, discovered it was stemming from the last entry number and the formula in cell, 'L4', that was recording the highest number for the next entry.... both were in column L,,, this appears to have caused a conflict in the 'findvalue'.... after moving the equation in L4 to M4, everything worked as expected....

    many thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 02-11-2015, 04:00 PM
  2. [SOLVED] Limit number entry in Userform TextBox to exactly 6 digits
    By Twaddy006 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-04-2014, 10:53 AM
  3. [SOLVED] Restricting textbox entry to a less than a given number
    By WML0068 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-30-2012, 09:29 PM
  4. Excel 2007: Userform Textbox Prevent Number as the first entry
    By Hudas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-20-2012, 02:48 PM
  5. Unwanted change in width of columns when showing formula.
    By Erik Cardell in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 04-11-2006, 04:50 PM
  6. Showing a unique random number w/o duplicates
    By tx12345 in forum Excel Formulas & Functions
    Replies: 59
    Last Post: 09-06-2005, 09:05 PM
  7. graphs showing unwanted zero values
    By Zadig Galbaras in forum Excel General
    Replies: 2
    Last Post: 06-01-2005, 06:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1