Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    EXCEL/VBA 2003 (Which is better to use?)

    In column A8 through A88, I have a list of names (john, paul, ringo, george, etc etc)
    I'm trying to get it so that within either a listbox or combobox, when the user clicks on the jobname a new userform will pop up and on that form the user will be able to input his/her data .

    I'm not quite sure which one would be a better fit.

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

    Re: EXCEL/VBA 2003 (Which is better to use?)

    You can create a list box or combo box that lists the names - either will work. But I wouldn't display a new form when the user clicks on an item - that leaves little room for mistakes. I'd put a command button next to the list box or combo box. That way the user can select an item, if necessary select a different one, and then click the button.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL/VBA 2003 (Which is better to use?)

    Question for you, where do I put the listbox. my book is talking about form_load but i cannot find that in the declarations.

    Option Explicit

    Private SubfrmEZJOBS_LOAD()

    Me.ListBox1.Item.Add "EZ0772B"
    Me.ListBox1.AddItem "EZ0773B"
    Me.ListBox1.AddItem "EZ0775"
    Me.ListBox1.AddItem "EZ0018"
    Me.ListBox1.AddItem "EZ0114"
    Me.ListBox1.AddItem "EZ0916"
    If STRJOBNAME <> "" Then MsgBox "JOBNAME WAS FOUND"
    If STRJOBNAME <> "" Then MsgBox "JOBNAME NOT FOUND IN DATABASE"


    End Sub


    Private Sub CommandButton3_Click()

    Dim C As Range
    For Each C In Sheets("FEB29").Range("A8:A88")
    If C.Value = ListBox1.Value Then C.Offset(, 4).Value = "NR"
    Next C
    End Sub

    am i doing this correctly

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

    Re: EXCEL/VBA 2003 (Which is better to use?)

    The Load event is for Access, not for Excel. Use the UserForm_Initialize event of the userform to populate list boxes and combo boxes.

    I don't understand the lines

    If STRJOBNAME <> "" Then MsgBox "JOBNAME WAS FOUND"
    If STRJOBNAME <> "" Then MsgBox "JOBNAME NOT FOUND IN DATABASE"

  5. #5
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL/VBA 2003 (Which is better to use?)

    i just wanted to see where it went thats all but unfortunatley my list box is not being populated.

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

    Re: EXCEL/VBA 2003 (Which is better to use?)

    Did you put the code in the userform's own module? It shouldn't be in a standard module.
    When you open a userform in the Visual Basic Editor, you can switch to its module by selecting View | Code.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL/VBA 2003 (Which is better to use?)

    OK, i got it to populate ok. my problem now i'm trying to subtract two different times. txtStime and txtEtime.

    Dim actrt As Integer

    If Me.txtSTime.Value <> "" And Me.txtETime.Value <> "" Then

    actrt = Me.txtSTime - Me.txtETime
    ws.Cells(irow, 5).Value = actrt


    what is incorrect here?

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

    Re: EXCEL/VBA 2003 (Which is better to use?)

    Times are stored as a fraction of 1 day, for example 6 AM = 0.25 (one quarter of a day), and 12:00 PM (noon) = 0.5 (one half of a day). So you cannot use Integer as data type. Define actrt as

    Dim actrt As Date

    Moreover, you must convert the values of the text boxes to date/time values:

    actrt = CDate(Me.txtETime) - CDate(Me.txtSTime)

    (I assumed that ETime = end time and STime = start time, you should subtract the start time from the end time; if I am mistaken, keep the original order)

  9. #9
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL/VBA 2003 (Which is better to use?)

    Hans that did not work for me.... knowing me I probably did something wrong, but please allow me to better expalin what I'm trying to do here.

    I have 5 columns

    column b = average run time, this column will always remain the same has fixed numbers in it.
    (example 0:01)

    column c = the start time, the user will input this through the userform(txtStime.value).

    column d = the end time , the user will input this through the userform(txtEtime.value).

    column e = the actual runtime, this will subtract column d from column c.

    column f = exceeded run time, this will subtract column e from column b to check to see if the runtime exceeded the average runtime allowed in column b.


    I have never done this with the time before. so any assistance would be appreciated.

    also I have a listbox populated with a bunch of job names (bob, joe, phil etc...), I'm trying to get it so that when the user selects the jobname the correct time they input will go with the correct row that the jobname is located on. any ideas?

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

    Re: EXCEL/VBA 2003 (Which is better to use?)

    I gather that column B is already filled in. You coulds also fill columns E and F with formulas, so that the code doesn't have to perform any calculations.

    See the attached sample workbook and look at the formulas in columns E and F. They only display a result if the start and end times are filled in.

    You can use the Find method to find the row for the name.
    Attached Files Attached Files

Posting Permissions

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