Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Lounger
    Join Date
    Jun 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Userform textbox auto fill

    Hello,

    Attached is the spreadsheet I will be refferencing. As you can see, on sheet 1 I have a bunch of command buttons that pull up a userform. What I am trying to figure out isthere a way that when the userform form is pulled up is takes the corrisponding asset name (in column A) with it and fills the asset textbox on the form with it.

    Any help would be great. THANKS!!!

    JoshNEW Route.xlsm

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,901
    Thanks
    190
    Thanked 719 Times in 655 Posts
    Josh,

    Welcome to the Lounge as a new poster!

    A couple of questioins:
    1. Will all of the buttons call the SAME form?
    2. If so do you have any reason not to use a SINGLE button?

    If the answers are Yes and No respectively you can easily setup the workbook to have the user select the item in column A then click the single button and then use the UserForm_Initialize event to grab the value of the ActiveCell and use in in the form as necessary.
    Josh.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,049
    Thanks
    39
    Thanked 182 Times in 169 Posts
    Josh,
    Looking at your code to populate your list box could have been much simplified with the following code instead of adding each item as a string in a line of code

    Code:
    For I = 2 To 141
         .AddItem Cells(I, 1)
    Next I
    If you wish to keep all the buttons and not go with RG's suggestion then to add a certain text to a textbox on a form when opening the form, the best way is to add to the sheets button's click event a line that dictates what the AssetTextBox value will be. Assuming that CommandButton1 is adjacent to cell A2 and you added a textbox to the form called AssetTextBox.

    Code:
    Private Sub CommandButton1_Click()
    UserForm.AssetTextBox = Cells(2, 1) 'ADD TO EACH BUTTON'S CODE BUT CHANGE THE ROW
    UserForm.Show
    End Sub
    HTH,
    Maud

  5. #4
    New Lounger
    Join Date
    Jun 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Josh,
    Looking at your code to populate your list box could have been much simplified with the following code instead of adding each item as a string in a line of code

    Code:
    For I = 2 To 141
         .AddItem Cells(I, 1)
    Next I
    If you wish to keep all the buttons and not go with RG's suggestion then to add a certain text to a textbox on a form when opening the form, the best way is to add to the sheets button's click event a line that dictates what the AssetTextBox value will be. Assuming that CommandButton1 is adjacent to cell A2 and you added a textbox to the form called AssetTextBox.

    Code:
    Private Sub CommandButton1_Click()
    UserForm.AssetTextBox = Cells(2, 1) 'ADD TO EACH BUTTON'S CODE BUT CHANGE THE ROW
    UserForm.Show
    End Sub
    HTH,
    Maud
    Thanks Maudibe,

    I used your .AddItem and it worked great!! I am not keen on inserting specific code for each button as the doc I posted is only one of 10 that I will have to do and with 140 button each......man I am tired just thinking about it. I am hoping that RG 's method will work. Thanks for your help on this.

    Josh

  6. #5
    New Lounger
    Join Date
    Jun 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Josh,

    Welcome to the Lounge as a new poster!

    A couple of questioins:
    1. Will all of the buttons call the SAME form?
    2. If so do you have any reason not to use a SINGLE button?

    If the answers are Yes and No respectively you can easily setup the workbook to have the user select the item in column A then click the single button and then use the UserForm_Initialize event to grab the value of the ActiveCell and use in in the form as necessary.
    Josh.JPG

    HTH
    Thanks RetiredGeek,

    I was able to get this to work but it isnt filling the assettextbox with the selected text. I do, however like the selection verification. I need to point out that I am very new to VBA and have taught myself the little I know so if I am missing a step please explain.

    The answers to your questions are....either way will work as long as I am error proofing the process for the users. This is for a PM route and the gentlemen that will be using it are not computer savy (not that I am either) and I want them to concentrate on the asset not the mechanisim of reporting. So if it is better to go with one button and I can explain the process that will work just fine.

    I will eventually have to create different userforms as different assets will require different inspections. For now, I was going to just start with the one userform.

    I cant thank you enough for your help.
    Josh

    P.S. Thanks for the beer!

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,901
    Thanks
    190
    Thanked 719 Times in 655 Posts
    Josh,

    You just need to replace ( or just add after) the msgbox statement with: TextBox1.Value = ActiveCell.Value
    Josh.JPG
    Of course you will replace TextBox1 with the name you assign to the approximate text box.
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  8. #7
    New Lounger
    Join Date
    Jun 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Josh,

    You just need to replace ( or just add after) the msgbox statement with: TextBox1.Value = ActiveCell.Value
    Josh.JPG
    Of course you will replace TextBox1 with the name you assign to the approximate text box.
    HTH
    Worked PERFECTLY!! Thank you Sir!

  9. #8
    New Lounger
    Join Date
    Jun 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have another question if you have the time. Is there a way to insert some function that tells if a asset has been inspected ie, turn something a different shade of color or anything at all?

  10. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,192
    Thanks
    14
    Thanked 325 Times in 319 Posts
    You can use conditional formatting to change the colors of a cell based on the values in another cell.

    Do you have an example in mind for us to provide more details?
    Steve

  11. #10
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,049
    Thanks
    39
    Thanked 182 Times in 169 Posts
    Clicking the Submit button could change the color of the active cell

    Add this line to click event for the Submit button:

    Activecell.interior.color=vbYellow

  12. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,870
    Thanks
    0
    Thanked 79 Times in 75 Posts
    BTW, it's easier and faster to populate your listbox like this:
    Code:
    AssetListBox.List = Sheets("sheet1").Range("A2:A141").Value
    Regards,
    Rory
    Microsoft MVP - Excel.

  13. #12
    New Lounger
    Join Date
    Mar 2002
    Location
    Virginia
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by scubajay30 View Post
    Hello,

    Attached is the spreadsheet I will be refferencing. As you can see, on sheet 1 I have a bunch of command buttons that pull up a userform. What I am trying to figure out isthere a way that when the userform form is pulled up is takes the corrisponding asset name (in column A) with it and fills the asset textbox on the form with it.

    Any help would be great. THANKS!!!
    Open the VBA editor.

    "View Code" of the UserForm in question.

    Insert a subroutine in the code window named:
    Private Sub UserForm_Initialize()

    Insert in the subroutine the code statements that you wish to initialize the form. E.g. TextBoxA.text = [whatever]

    The statements can be almost anything, not only initial/default responses to data requirements, but even changing the properties of controls on the UserForm, etc.

    The code statements will be executed whenever the UserForm is opened.

    The subroutine can also be called at any time using the normal procedure for calling a sub.

    (I hope I have typed this stuff in the correct location. I don't find any other place for typing in an answer.)

    Fred Holmes
    Last edited by RetiredGeek; 2014-06-19 at 18:34. Reason: Added end quote tag

  14. #13
    New Lounger
    Join Date
    Jun 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RouteWorking.xlsmThank you everyone for the help. I have used just about al of your inputs. Attached is what I have so far. Please feel free to critique it and give me suggestions to make it better.

  15. #14
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,049
    Thanks
    39
    Thanked 182 Times in 169 Posts
    ScubaJay,

    Very clean and nicely done. I have a couple of suggestions:

    1. Add some error handling to your SendSheet Routine. Even though you may have a default email client installed the code will fail if the server or the program is not available.
    Code:
    Sub SendSheet()
    On Error GoTo errorhandler
    ThisWorkbook.Sheets(2).Copy
        With ActiveWorkbook
    
                 Dim Names()
                 Names = Array("josh.morris@mosaicco.com", "thomas.price@mosaicco.com")
                .SendMail Recipients:=Names(), _
                 Subject:=Format(Date, "mm/dd/yy") & " Utilities PM Route Data Sheet"
                .Close SaveChanges:=False
    
        End With
    Exit Sub
    errorhandler:
    MsgBox "There is no default email client on the computer.  This function has been disabled"
    End Sub
    Because I do not have an email client installed, I could not test further if this function worked but I was able to open multiple copies by multiple clicks of the Email button. If this was not intentional then you might want to add some checking to see if a copied sheet is currently open.

    2. You have 138 buttons that will all open the form and insert the same active cell's value in the asset text box. This is bloating and slowing your spreadsheet, taking up real estate, increase maintenance, un-needed code. You can delete all of them except one. ------OR--------

    3. An alternative is to remove all buttons and place the following code on the worksheet's module. Clicking on any asset cell will open the form and fill the asset textbox
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("A3:A72")) Is Nothing Or _
        Not Intersect(Target, Range("C3:C72")) Is Nothing Then _
            UserForm.Show
    End Sub
    4. A more efficient way to clear the data sheet would be
    Code:
    Sub Clear_Sheet3()
    ButtonChosen = MsgBox("Are you sure you want to clear all data?", vbQuestion + vbYesNo + vbDefaultButton2, "Continue?")
    If ButtonChosen = vbYes Then
        With Worksheets("Sheet3")
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        .Range("A3:P" & LastRow).ClearContents
        End With
    End If
    End Sub
    5. In your CommandSubmitButton code, there is a more reliable way to find the next available row and I would reference the data sheet using the With statement instead of activating it then deactivating it.
    Code:
    Private Sub SubmitCommandButton_Click()
    Dim emptyRow As Long
    With Worksheets("Sheet3")
    emptyRow = .Cells(1, 7).End(xlDown).Row + 1
    'Transfer information
    .Cells(emptyRow, 1).Value = DateTextBox.Value
    .Cells(emptyRow, 2).Value = AssetTextBox.Value
    
    If OperatingOptionButton1.Value = True Then
        .Cells(emptyRow, 3).Value = "Yes"
    Else
        .Cells(emptyRow, 3).Value = "No"
    End If
    If GuardsOptionButton1.Value = True Then
        .Cells(emptyRow, 4).Value = "Yes"
    Else
        .Cells(emptyRow, 4).Value = "No"
     End If
    If OilLevelsOptionButton1.Value = True Then
        .Cells(emptyRow, 5).Value = "Yes"
    Else
        .Cells(emptyRow, 5).Value = "No"
      End If
    If OtherOptionButton1.Value = True Then
        .Cells(emptyRow, 6).Value = "Yes"
    Else
        .Cells(emptyRow, 6).Value = "No"
    End If
    .Cells(emptyRow, 7).Value = MIBTempTextBox.Value
    .Cells(emptyRow, 8).Value = MIBTempTextBox.Value
    .Cells(emptyRow, 9).Value = MotorIPSTextBox.Value
    .Cells(emptyRow, 10).Value = MotorGTextBox.Value
    .Cells(emptyRow, 11).Value = DEIBTempTextBox.Value
    .Cells(emptyRow, 12).Value = DEOBTempTextBox.Value
    .Cells(emptyRow, 13).Value = DEIPSTextBox.Value
    .Cells(emptyRow, 14).Value = DEGTextBox.Value
    .Cells(emptyRow, 15).Value = ActionsTakenTextBox.Value
    .Cells(emptyRow, 16).Value = CommentsTextBox.Value
    End With
    Unload Me
    End Sub
    Very good job!

    Maud
    Attached Files Attached Files

  16. #15
    New Lounger
    Join Date
    Jun 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    ScubaJay,

    Very clean and nicely done. I have a couple of suggestions:

    1. Add some error handling to your SendSheet Routine. Even though you may have a default email client installed the code will fail if the server or the program is not available.
    Code:
    Sub SendSheet()
    On Error GoTo errorhandler
    ThisWorkbook.Sheets(2).Copy
        With ActiveWorkbook
    
                 Dim Names()
                 Names = Array("josh.morris@mosaicco.com", "thomas.price@mosaicco.com")
                .SendMail Recipients:=Names(), _
                 Subject:=Format(Date, "mm/dd/yy") & " Utilities PM Route Data Sheet"
                .Close SaveChanges:=False
    
        End With
    Exit Sub
    errorhandler:
    MsgBox "There is no default email client on the computer.  This function has been disabled"
    End Sub
    Because I do not have an email client installed, I could not test further if this function worked but I was able to open multiple copies by multiple clicks of the Email button. If this was not intentional then you might want to add some checking to see if a copied sheet is currently open.

    2. You have 138 buttons that will all open the form and insert the same active cell's value in the asset text box. This is bloating and slowing your spreadsheet, taking up real estate, increase maintenance, un-needed code. You can delete all of them except one. ------OR--------

    3. An alternative is to remove all buttons and place the following code on the worksheet's module. Clicking on any asset cell will open the form and fill the asset textbox
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("A3:A72")) Is Nothing Or _
        Not Intersect(Target, Range("C3:C72")) Is Nothing Then _
            UserForm.Show
    End Sub
    4. A more efficient way to clear the data sheet would be
    Code:
    Sub Clear_Sheet3()
    ButtonChosen = MsgBox("Are you sure you want to clear all data?", vbQuestion + vbYesNo + vbDefaultButton2, "Continue?")
    If ButtonChosen = vbYes Then
        With Worksheets("Sheet3")
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        .Range("A3:P" & LastRow).ClearContents
        End With
    End If
    End Sub
    5. In your CommandSubmitButton code, there is a more reliable way to find the next available row and I would reference the data sheet using the With statement instead of activating it then deactivating it.
    Code:
    Private Sub SubmitCommandButton_Click()
    Dim emptyRow As Long
    With Worksheets("Sheet3")
    emptyRow = .Cells(1, 7).End(xlDown).Row + 1
    'Transfer information
    .Cells(emptyRow, 1).Value = DateTextBox.Value
    .Cells(emptyRow, 2).Value = AssetTextBox.Value
    
    If OperatingOptionButton1.Value = True Then
        .Cells(emptyRow, 3).Value = "Yes"
    Else
        .Cells(emptyRow, 3).Value = "No"
    End If
    If GuardsOptionButton1.Value = True Then
        .Cells(emptyRow, 4).Value = "Yes"
    Else
        .Cells(emptyRow, 4).Value = "No"
     End If
    If OilLevelsOptionButton1.Value = True Then
        .Cells(emptyRow, 5).Value = "Yes"
    Else
        .Cells(emptyRow, 5).Value = "No"
      End If
    If OtherOptionButton1.Value = True Then
        .Cells(emptyRow, 6).Value = "Yes"
    Else
        .Cells(emptyRow, 6).Value = "No"
    End If
    .Cells(emptyRow, 7).Value = MIBTempTextBox.Value
    .Cells(emptyRow, 8).Value = MIBTempTextBox.Value
    .Cells(emptyRow, 9).Value = MotorIPSTextBox.Value
    .Cells(emptyRow, 10).Value = MotorGTextBox.Value
    .Cells(emptyRow, 11).Value = DEIBTempTextBox.Value
    .Cells(emptyRow, 12).Value = DEOBTempTextBox.Value
    .Cells(emptyRow, 13).Value = DEIPSTextBox.Value
    .Cells(emptyRow, 14).Value = DEGTextBox.Value
    .Cells(emptyRow, 15).Value = ActionsTakenTextBox.Value
    .Cells(emptyRow, 16).Value = CommentsTextBox.Value
    End With
    Unload Me
    End Sub
    Very good job!

    Maud
    OK Thank you for all of that. I absolutly love your suggestions. I took what you suggested and made some changes but am getting an error now.

    So What I did was create another userform (eventually I will be creating a lot more userforms as we create different asset stratagies) named ACUserform2 and I have it being pulled up from cells C55, C57, and C60. The correct userform is being initialized but when I hit submit, I get Error 1004. I dont understand why this error is happening as it is the same code from the other form and it works just fine there.
    RouteWorkingnew.xlsm

    Thank you so much for your help!!!

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
  •