Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    New Lounger
    Join Date
    May 2008
    Location
    Louisville, Kentucky, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Userform (Excel 2003)

    Subject edited by HansV to help in future searches

    Hello one and all My name is Jay Vimcenta and as my moniker states I'm lost in Excel. I found this site in a book my friend at school loaned me called excell 2003. I hope somebody out there can assist me with this little Problem I have.

    I made a worksheet with Three different user forms. The first form inputs data to the worksheet, The second form updates the worksheet, and the third form helps with some minor calculations.
    I have it the way that I need it to work But I cannot use any already established macro's in this project. What I need is something like control F to have the program within the second user form to check column 2 and column 4. What I have is multiple names like jon, jim, harry, Eiffel tower etc.. and a series of numbers attached to each one.

    So in colimn 2 I have jim and in column 4 I have the number 1.

    But I need to be able to find Jim and 7 and do the calculation for him. My problem is Every time the user would update in form 2 (input box one = jim) it would look at only jim / 1 and stop. I need it to keep looking at all the Jims in column 2 and compare it to column 4 to ensure that it is the correct Jim I'm looking for.

    I figure if I add another user input box on the form the user would have to input his/her number. But that still won't be able to look in both columns for me and this is where I am lost.

    Please keep in mind I am not allowed to use any macro's linke cntl&f

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

    Re: Userform (Excel 2003)

    Welcome to Woody's Lounge!

    A text box on a userform can either be linked to a specific cell through its ControlSource property, or it can be unbound, but then you need VBA code to do something with it. Without VBA code it's not possible to have the userform look up values...

  3. #3
    New Lounger
    Join Date
    May 2008
    Location
    Louisville, Kentucky, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Userform (Excel 2003)

    Dear Hans

    Thank you for your comments and information. But again I'm still lost.
    this is what I have done so far using both Excel and Vba

    User form 1
    I have coded my first inputbox (ibox1) to add the data into column 2. Works fine.
    I have coded my second inputbox (ibox2) to add data into column 4. works fine.

    this is where my problem resides at.
    User form 2
    if the user wants to update column 5 (comments on why they were here) then
    the user would type their name into input box1 (ibox1), their number in input box2 (ibox2)

    the code that I need with check to ensure both the input box1 and input box 2 are correct and on the same line then it would add their comments to column 5 of that same line.

    I not that knowledgable about this and my research is kind of hazy, so how would you go about doing something like this

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

    Re: Userform (Excel 2003)

    You can use code like this as a starting point:

    Dim strVal1 As String
    Dim strVal2 As String
    Dim r As Long
    Dim m As Long

    ' Get values of text boxes
    strVal1 = Me.ibox1
    strVal2 = Me.ibox2

    ' Last used row
    m = Range("B" & Rows.Count).End(xlUp).Row

    ' Loop through the rows
    For r = 1 To m
    ' Do we have a match?
    If Range("B" & r) = strVal1 And Range("D" & r) = strVal2 Then
    ' If so, get out of loop
    Exit For
    End If
    Next r

    If r > m Then
    MsgBox "Combination of values not found", vbInformation
    Else
    MsgBox "Combination found in row " & r, vbInformation
    End If

    Instead of the second message box, you'd insert code to update the value in row r and column 5.

  5. #5
    New Lounger
    Join Date
    May 2008
    Location
    Louisville, Kentucky, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Userform (Excel 2003)

    Dear Hans.

    Thank you again for your assistance, you seem to be the only person responding to my request for assistance.

    I added your code but it does not allow me to add the comments (input box 3) column H.

    The code that I have apparently is not working with your code.

    <code>

    ws.Cells(irow, 8).Value = me.ibox3.value

    </code>

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

    Re: Userform (Excel 2003)

    The code I posted uses a variable named r for the row, yours uses a variable named irow. So to incorporate my code in yours, you'll have to replace r with irow.
    Also, I assumed that the code would operate on the active worksheet, you're using a worksheet variable ws. So where I used Range(...) you should change it to ws.Range(...).

  7. #7
    New Lounger
    Join Date
    May 2008
    Location
    Louisville, Kentucky, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Userform (Excel 2003)

    Dear Hans, Thank you so much. after tinkering around with the code you gave me. I was able to get it done.

    But I do have one more question if you really do not mind.

    This workbook of mine will grow with each month we are in class.

    So how do I get the user to use the correct worksheet. (May through August)
    A worksheet is needed for each month to show how each one of my group is progressing. and how many times they had problems.

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Userform (Excel 2003)

    You can add a Workbook_Open event:
    1. <LI>Open your Excel Workbook, I'm assuming that your sheet names are spelled out: July not Jul
      <LI>Press the Alt key and the F11 key at the same time to go to VBA
      <LI>In the Project Explorer window (upper left) double-click ThisWorkbook
      <LI>Enter the code below
    <pre>Private Sub Workbook_Open()
    On Error Resume Next
    Worksheets(Format(Now, "MMMM")).Activate
    End Sub
    </pre>

    Now, whenever you open the workbook, it will open to this month's sheet. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    New Lounger
    Join Date
    May 2008
    Location
    Louisville, Kentucky, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Userform (Excel 2003)

    Hello SammyB, Happy Memorial day to you. Thank you for you input on this matter but I don't think I explained myself well.

    Please let me regress, I have the worksheets May through august.
    I have three user forms, One the user will input the data through
    the second one, the user will be able to update their data and progress through
    and the third user form I will be able to judge how far each member of my group has gone.

    Now Hans was Great in giving the code to get me started and after tinkering with it like he stated it is working fine.

    My problem is that it only works for this current month. I need the user to be able to update (user form 2) their data and have the updated information go to the same month and same line as the original data - so that I can track each member during each month,

    I hope this gives a better understanding of what I am trying to accomplish.

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

    Re: Userform (Excel 2003)

    The single line of code that you've shown us uses a variable ws. What value is assigned to ws?

  11. #11
    New Lounger
    Join Date
    May 2008
    Location
    Louisville, Kentucky, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Userform (Excel 2003)

    Hello again Hans, and Happy Memorial day to you

    ws is assigned the name of the month (ie... May)

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

    Re: Userform (Excel 2003)

    Perhaps you should let the user activate the worksheet he or she wants to use, and set ws to the active sheet, whatever it may be:

    Set ws = ActiveSheet

    So if the user activates the sheet for March, then runs the code that displays the userform, ws will be set to the sheet for March, and if the user activates the sheet for August, ws will be set to the sheet for August.

  13. #13
    New Lounger
    Join Date
    May 2008
    Location
    Louisville, Kentucky, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Userform (Excel 2003)

    Does the code go in the workbook or each individual worksheet?

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

    Re: Userform (Excel 2003)

    I assume that it goes in the code module for the userform - that's where you use it, don't you?

  15. #15
    New Lounger
    Join Date
    May 2008
    Location
    Louisville, Kentucky, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Userform (Excel 2003)

    Dear Hans

    You are correct, but I have tried that and it does not seem to work

    <code>

    Dim ws As Worksheet
    Set ws = ActiveSheet

    </code>

Page 1 of 2 12 LastLast

Posting Permissions

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