Posts by Robert Conklin

  • Command Button Security

    I have a command button on a userform called "Approve". I have a list of users that are authorized to Approve any of the information that has been entered into the userform. I have attempted to write some code to handle it, but it is not working. When an authorized user clicks the "Approve" button the "Approved By" field on the userform should be populated with the Windows username. Can anyone take a look at the code and show me what is wrong? Thanks in advance!!


  • Auto-Fill Userform Text Boxes based on a Combobox selection

    Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    Ok, I figured out the above. I just deleted the field and created a new one. Now it works fine. Now, for some reason in the code above, the ID field does not cycle to the next number after an entry is sent and the List Box "Click to Review" field is not populating. Any suggestions?


    I also noticed that you inserted a table into the worksheet. How did you set that up?

  • Auto-Fill Userform Text Boxes based on a Combobox selection

    Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    Hey Dotchiejack, I have begun implementing code to the rest of my project. Can you take a look at this code and tell me why I am unable to enter data into my "New Part Description" field (T_10)?


  • VBA to transfer data from one workbook to another

    With the help of others, I have created a workbook for my end users that allows them to submit spare part maintenance requests. The problem lies in the fact that we have 48 different locations that will be submitting these requests. Is it possible to create a command button that will only work for specified Windows user names, that when clicked will copy all of the data from the workbook to another workbook on a shared drive? In order to satisfy our internal audit we have to be able to track all of the changes, and having one location to look would take care of that.

  • Auto-Fill Userform Text Boxes based on a Combobox selection

    Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    GREAT NEWS!! I was able to figure out myself how do correct all of the above. I left the "Change" Command button because I figured out how to use it. I added code to fill in the "Lot Size" field. I also added the "Approve" command button and the code to make it work. Will take a look at the code and make sure that everything looks ok?

  • Auto-Fill Userform Text Boxes based on a Combobox selection

    Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    Ok, I tried the code in my spreadsheet, but it is still not working. I will make this simple on both of us. I am going to use your original spreadsheet. Everything is already working and I am running out of time. I would like to make a few changes.


    1. I would like to condensed the Equip. Functional Location fields (down from 6 to 1. I will have the end users separate each number with a comma. That will cut out the concatenated field on the user form and the extra columns in the worksheet.


    2. The "Lot Size" (T_11) needs to autofill with "HB" when "VB" is selected in the "MRP Type" combobox.


    3. I would like to change the "Change" command button to "Approve" and when clicked autofill the "Approved By" field (T_24) and the cells in column X on the ADD-Extend worksheet.


    4. Can you label what each sub does so I can reference it for the other worksheets involved in this project?

  • Auto-Fill Userform Text Boxes based on a Combobox selection

    Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    Thanks for the tip dotchiejack! I tried to incorporate your code into my workbook to fill my LBL_Review field like you did your LB_01 field, but I am missing something...



    Also, I tried to use your code for what I think is the "Clear Fields" command button, but that is not working either.

    Code
    Private Sub_CMB_Clear_Click()'Clear ALL Fields
    For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "Combobox" Then Ctrl.Value = ""
    Next Ctrl
        LBL_Review.ListIndex = -1
        LBL_Review.TopIndex = 0
    Call UserForm_Initialize
End Sub


    I was however able to get the "Approve" button to work, but not exactly like I would have it. I would like it to post to the Approved By field in the userform, and its respective cell in the ADD-EXTEND worksheet as well. As it stands right now, when I click Approve, it posts it to the Approved By field, and then I click submit to post the same to the worksheet.


    I also was not able to decipher the code to get the ID to post automatically or the userform to clear whenever the "Submit Button" is clicked. I liked the pop up confirmation messages also.

  • Auto-Fill Userform Text Boxes based on a Combobox selection

    Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    Gentlemen, this was not supposed to turn into a pissing match. I apologize if I turned it into one. From what I have learned, there are many different ways to get to what you want to do in VBA, and you two both have a different way to get there. They are both right, and they both work. But for my immediate needs, the shorter and clearer the better. Both of you have helped me more than I can ever say. I have learned from both of you. As I have told royUK, I am learning VBA, but the time frame that I have to complete this project is too short for me to get a solid grasp on the language in order to complete it myself. This is the reason I have been asking so many questions. As of today, I only have two more weeks to complete the project and what we have been working on is the bulk. I can hopefully take what you guys have helped with and what I have learned and apply it to the rest of the larger workbook. Again, I apologize if I started any trouble.

  • Auto-Fill Userform Text Boxes based on a Combobox selection

    Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    Now why would I be getting an error message when I copy/pasted the code from the "Short Code" spreadsheet above to another spreadsheet and set it up exactly like the above spreadsheet. When I click the Add-Extend button I get the following error message:


    Run-time Error '424'
    Object Required


    It points to the 'Sub Userform_Activate()' and references line 'Me.Plant_Name.List = Range(Cells(2, 2), .Cells(.Rows.Count,1).end(X1UP)).Value'


    Both workbooks are identical, but the newer version gives the error message.

  • Auto-Fill Userform Text Boxes based on a Combobox selection

    Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    Thanks to the phenomenal coding by dotchiejack, I was able to take what I learned from his code (which to be honest was a bit intimidating), but was able to apply some of it to royUK's more condensed code almost to completion.


    The only thing I lack is the ID field and how it works.
    The LBL_Review field at the bottom of the userform and how it works.
    The close button should close the userform.
    The clear fields button should clear the userform, but keep the same ID number.
    Also, instead of a Change button, I would like to change it to an Approve button that would fill in the Approved By field with the windows username. The kicker is, that button should only be used certain people who have authorization to approve the requests.


    The workbook labeled "Long Code" is what dotchiejack created, and it works like a champ. My goal is to get the same thing working in the "Short Code" workbook but in a condensed easy to understand code. Again, I REALLY appreciate everything that you guys have done and are doing to help!!

  • Auto-Fill Userform Text Boxes based on a Combobox selection

    Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    Ok, I have moved all of my other worksheets to the working copy that you sent me. I have not changed anything in your code other than adding the date and time to the Userform_Initialized sub and make some corrections to the cell placement in LB_01_Click sub. Now when I click an entry in the "Click to Change" box at the bottom, I get the following error.


    Run-time Error '-2147352571 (80020005)'
    Could not set the value property. Type Mismatch


    The error message is in the LB_01_Click sub, line T_21.Value = LB_01.Column(33). I looked at both the value on the worksheet and the value in the property box for the textbox and they match. What am I missing?