Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  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)

    I have one workbook. Two user forms I'm looking to have a click event that asks if the user wants to hold a job. If yes userform1 appears if no then userform2 appears. I'm thinking some kind of if statement. but I'm not sure how to code the userforms so they open up.

    thank you

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: excel/vba (2003)

    Hi There

    Without the names of your forms etc, please use my code as an example of one way of doing it. I am assuming you have 3 forms, frm1, frm2 and frm3.

    On frm3 there would be a combobox which I have set to show Yes or No. When you make a selection in the combo I have it so that either frm1 becomes visible and frm2 goes "invisible"

    <pre>
    Private Sub UserForm_Initialize()

    Load frm1
    Load frm2


    cboChoice.AddItem "Yes" 'ListIndex = 0
    cboChoice.AddItem "No" 'ListIndex = 1

    cboChoice.Style = fmStyleDropDownList

    End Sub



    Private Sub cboChoice_Change()
    On Error Resume Next
    If Me.cboChoice = "Yes" Then
    frm1.Show
    frm2.Hide
    ElseIf Me.cboChoice = "No" Then
    frm1.Hide
    frm2.Show
    End If

    End Sub</pre>



    Is this what you are looking for?
    Jerry

  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)

    yes thank you

  4. #4
    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)

    Ok one last thing. Does anybody know how to do a search within vba/excel?

    I have two user forms the first form allows the user to add to the ws any job that needs to be held by the user. it includes date, jobname, etc... and end at column 9.

    The second userform is for the user to update the spreadsheet, another words to release that same job that they put on hold earlier.

    Here lies my problem:
    I need the information that is inputted from userform 2 to be placed on the same row as the information from user form1 starting in column 10. So that when the user inputs the jobname on user form2 it will know what row to go to or if it's not within this database then msgbox " jobname not found"

    How do I do this? I guess it would be some kind of a search but not sure since I have never done a search before in vba.

    Any help with this matter would be GREATLY APPRECIATED.

    thank you

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

    Re: excel/vba (2003)

    Let's say that the job name is in column B. You can use something like this:
    <code>
    Dim strJobName As String
    Dim oCell As Range
    Dim r As Long

    ' Get job name from text box on form
    strJobName = Me.txtJobName
    ' Try to find job name in column B
    Set oCell = Range("B:B").Find(What:=strJobName, LookIn:=xlValues, LookAt:=xlWhole)
    ' If not found ...
    If oCell Is Nothing Then
    ' ... inform user and get out
    MsgBox "Job name not found!", vbExclamation
    Exit Sub
    End If
    ' Row number of cell containing job name
    r = oCell.Row</code>

  6. #6
    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)

    Hello again my friend

    This is what I got so far but it does not work, it does not put the additional data in the same row as the original data from form1.


    Private Sub cmdRJFH_Click()
    'Dim iRow As Long
    Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
    Dim ws As Worksheet
    Set ws = Worksheets("feb 2008")
    'check for a job name!
    If Trim(Me.txtRLJname.Value) = "" Then
    Me.txtRLJname.SetFocus
    MsgBox "Please enter the Job name that has been requested to be taken off hold!"
    Exit Sub
    End If

    Dim JobName As String, Found As Range, iRow As Integer
    JobName = frmRLSEHOLD.txtRLJname.Text
    frmRLSEHOLD.Hide
    Set Found = ws.Columns(2).Find(what:=JobName)

    If Found Is Nothing Then
    MsgBox JobName & " Not Found"
    Exit Sub
    Else
    iRow = Found.Row
    ws.Cells(iRow, 9).Value = Me.txtRLJname.Value
    ws.Cells(iRow, 10).Value = Me.txtInitials.Value
    If Me.txtInitials.Value = "" Then
    Me.txtInitials.SetFocus
    MsgBox "Please enter your initials as requested!"
    End If
    Exit Sub

    ws.Cells(iRow, 11).Value = Me.txtNop2.Value
    If Me.txtNop2.Value = "" Then
    Me.txtNop2.SetFocus
    MsgBox "Please enter the Requestor's Name!"
    End If
    Exit Sub

    ws.Cells(iRow, 12).Value = Now
    ws.Cells(iRow, 13).Value = Me.txtCOMments.Value
    'clear the data
    Me.txtRLJname.Value = ""
    Me.txtInitials.Value = ""
    Me.txtNop2.Value = ""
    Me.txtCOMments.Value = ""
    Me.txtRLJname.SetFocus
    End If

    End Sub

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

    Re: excel/vba (2003)

    Is frmRLSEHOLD the form running the code? If so, I'd replace the references to frmRLSEHOLD with Me

    I'd change the line

    Set Found = ws.Columns(2).Find(What:=JobName)

    to

    Set Found = ws.Columns(2).Find(What:=JobName, LookIn:=xlValues, LookAt:=xlWhole)

    You twice have Exit Sub below and End If, I think it should be above the End If.

  8. #8
    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)

    ok I got that all straightened out, but since this is for the month of february is there a way that when the the month changes I can create a new folder.... Not a new workbook but a new folder that will contain the new workbook etc...

    Also is there a way to have it look for the current job of say job XYZ123 so that the code does not change all of them within that months worksheets. Another words say that the user wants to put a hold on job XYZ123 february 15th and release it on february 18th. Then anothe user comes around on February 22 and puts job XYZ123 on hold again. How will the code distinguish between which one for it to update. Which of course would the the current one.

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

    Re: excel/vba (2003)

    You can use the MkDir instruction to create a new folder, but are you sure it is a good idea to create a folder to hold a workbook for a specific month?

    I don't know what your worksheet looks like, so I cannot answer your second question.

  10. #10
    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)

    COLUMNA = DATE, COLUMN B = JOBNAME, COLUMN C = SDNUMBER, COLUMN D = REQUESTOR NAME, COLUMN E = USERHOLD, COLUMN F = INITIALS, COLUMNS G,H,I,J & K = COMMENTS (MERGED CELLS), COLUMN L = NAME OFPERSON TAKING OF HOLD, COLUMN M = DATE TAKEN OFF HOLD,

    SO IT WOULD BE SOMETHING LIKE THIS I GUESS.

    IF DATE = "" THEN
    Set Found = ws.Columns(2).Find(What:=JobName)


    BUT I'M NOT SURE....


    AND YES THEY WOULD LIKE EITHER A NEW WORKBOOK FOR EACH MONTH OR A NEW FOLDER FOR EACH MONTH.

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

    Re: excel/vba (2003)

    Go easy on the ALL CAPS please, it's the internet equivalent of shouting. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Do I understand correctly that the same job name can be repeated in column B?

  12. #12
    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)

    My sincere apologies, I have to type everyday in caps.

    Yes, the jobname can possibly be repeated within the same month.

  13. #13
    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)

    ok this does not work either?

    Set Found = ws.Columns(2).Find(WHAT:=JobName) And ws.Columns(9).Find(WHAT:="")

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

    Re: excel/vba (2003)

    You should let the user select a jobname and date. This could be done using one or two combo boxes.

    (It appears you're trying to develop a database in Excel. Wouldn't a database application such as Access be better suited for that?)

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

    Re: excel/vba (2003)

    You can't use Find that way. It searches in one range and for one value only.

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
  •