Userform textbox auto fill

Topic

New Reply

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!!!

Josh37210-NEW-Route

Reply | Quote

Replies

Josh,

Welcome to the Lounge as a new poster! :cheers:

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.
37211-Josh

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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!

Reply | Quote

I hit the wrong reply button. Sorry for the duplicate.

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!

Reply | Quote

Josh,

Welcome to the Lounge as a new poster! :cheers:

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.
37211-Josh

HTH :cheers:

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!

Reply | Quote

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) [COLOR=”#008000″]’ADD TO EACH BUTTON’S CODE BUT CHANGE THE ROW[/COLOR]
UserForm.Show
End Sub

HTH,
Maud

Reply | Quote

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) [COLOR=”#008000″]’ADD TO EACH BUTTON’S CODE BUT CHANGE THE ROW[/COLOR]
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

Reply | Quote

Josh,

You just need to replace ( or just add after) the msgbox statement with: TextBox1.Value = ActiveCell.Value
37215-Josh
Of course you will replace TextBox1 with the name you assign to the approximate text box.
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Josh,

You just need to replace ( or just add after) the msgbox statement with: TextBox1.Value = ActiveCell.Value
37215-Josh
Of course you will replace TextBox1 with the name you assign to the approximate text box.
HTH :cheers:

Worked PERFECTLY!! Thank you Sir!

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

BTW, it’s easier and faster to populate your listbox like this:

Code:

AssetListBox.List = Sheets("sheet1").Range("A2:A141").Value

Reply | Quote

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!!!
[/quote]

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

Reply | Quote

37243-RouteWorkingThank 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.

Reply | Quote

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

Reply | Quote

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.
37247-RouteWorkingnew

Thank you so much for your help!!!

Reply | Quote

Very simple resolution. In the code for the new form, change the 7 to a 9:
emptyRow = .Cells(1, 9).End(xlDown).Row + 1

You must select a column that counts down from row 2. Column 9-23 in the data sheet has a value in row 2 so changing the column to any of those will resolve your issue. If you look on the other data sheet, I chose 7 because it has a value in row 2 where the others are merged. If you were not using merged cells in the header row, any header column would be acceptable as long as it has a header value.

Maud

Reply | Quote

37402-Utilities-Route-working

OK I am back with another issue and hopefully you can help. I have recently lost the ability to use Outlook to email the report on a daily basis. SO the only way I can efficiently do this is come up with a script that when they are finish with the route it will take all of the data on sheets 2, 3, and 4 and insert it in another workbook. It would be nice if when the script pastes the dat it uses something like a lastcell or some other way of finding the first blank row so I can keep compiling the data in one sheet. I was wondering if you might lead me in the right direction as I have no idea where to even start. I know this is a lot to ask but if you just get me started I may be able to figure out the rest. Attached is what I have so far.

Reply | Quote

Scubajay,

Here is the code that should do what you want. Clicking on the Transfer button will open the destination workbook and transfer each row from sheets 2 through 4 in the source file to corresponding sheets in the destination file. The newly added rows in the destination file will be appended to any existing rows. Lastly, the macro will save and close the destination file with no user intervention.

You will need to change Path to the path of your destination file. It does not matter if the destination file is open or closed when the routine is initiated but either way, it will close and save upon completion. I have left some test data in the attached sample to play with

HTH,
Maud

Code:

Sub Transfer()
On Error Resume Next
[COLOR=”#008000″]’———————————————
‘DECLARE OBJECTS/VARIABLES[/COLOR]
    Dim Targetbook As Workbook
    Dim Sourcebook As Workbook
    Dim Path
[COLOR=”#008000″]’———————————————
‘OPEN TARGET WORKBOOK AND SET VARIABLE TO WORKBOOKS[/COLOR]
    Path = “C:UsersMaudibeDesktop”
    Workbooks.Open Filename:=Path & “Utilities DB.xlsx”
    Set Targetbook = Workbooks(“Utilities DB.xlsx”)
    Set Sourcebook = Workbooks(“Utilities Route working.xlsm”)
[COLOR=”#008000″]’———————————————
‘CYCLE THROUG SHEETS IN EACH WORKBOOK AND COPY DATA[/COLOR]
For I = 2 To 4
    LastRow = Sourcebook.Worksheets(I).Cells(Rows.Count, 2).End(xlUp).Row
    EndRow = Targetbook.Worksheets(I – 1).Cells(Rows.Count, 2).End(xlUp).Row
    LastRow = IIf(LastRow < 2, 2, LastRow)
    EndRow = IIf(EndRow < 2, 2, EndRow)
    Select Case I
        Case 2: col = 24
        Case 3: col = 27
        Case 4: col = 4
    End Select
    For J = 3 To LastRow
        For K = 1 To col
            Targetbook.Worksheets(I – 1).Cells(EndRow + 1, K) = Sourcebook.Worksheets(I).Cells(J, K)
        Next K
        EndRow = EndRow + 1
    Next J
Next I
[COLOR="#008000"]'——————————————–
'SAVE AND CLOSE TARGET WORKBOOK[/COLOR]
Application.DisplayAlerts = False
Targetbook.Close savechanges:=True
Application.DisplayAlerts = True
End Sub

Reply | Quote

WOW! once again you have amazed me!

I will buy you a beer if you can tell me how to make it check the target for duplicates before it inserts the data. Would I use a IF statement or is there a better way?

Reply | Quote

Scubajay,

I think this should do the trick…..Heineken!

Code:

Sub Transfer()
On Error Resume Next
[COLOR=”#008000″]’———————————————
‘DECLARE OBJECTS/VARIABLES[/COLOR]
    Dim Targetbook As Workbook
    Dim Sourcebook As Workbook
    Dim Path
[COLOR=”#008000″]’———————————————
‘OPEN TARGET WORKBOOK AND SET VARIABLE TO WORKBOOKS[/COLOR]
    Path = “C:UsersMaudibeDesktop”
    Workbooks.Open Filename:=Path & “Utilities DB.xlsx”
    Set Targetbook = Workbooks(“Utilities DB.xlsx”)
    Set Sourcebook = Workbooks(“Utilities Route working.xlsm”)
[COLOR=”#008000″]’———————————————
‘CYCLE THROUG SHEETS IN EACH WORKBOOK AND COPY DATA[/COLOR]
For I = 2 To 4
    LastRow = Sourcebook.Worksheets(I).Cells(Rows.Count, 2).End(xlUp).Row
    EndRow = Targetbook.Worksheets(I – 1).Cells(Rows.Count, 2).End(xlUp).Row
    LastRow = IIf(LastRow < 2, 2, LastRow)
    EndRow = IIf(EndRow < 2, 2, EndRow)
[COLOR="#008000"]'———————————————
'DETERMINE NUMBER OF COLUMNS ON SHEET[/COLOR]
    Select Case I
        Case 2: col = 24
        Case 3: col = 27
        Case 4: col = 4
    End Select
[COLOR="#008000"]'———————————————
'CYCLE THROUGH ROWS ON SHEETS[/COLOR]
    For J = 3 To LastRow
[COLOR="#008000"]    '———————————————
    'CHECK FOR DUPLICATES[/COLOR]
        For M = 3 To EndRow
            If Sourcebook.Worksheets(I).Cells(J, 2) = Targetbook.Worksheets(I – 1).Cells(M, 2) Then GoTo NextAsset
        Next M
[COLOR="#008000"]    '———————————————
    'COPY ASSET TO TARGET SHEET[/COLOR]
        For K = 1 To col
            Targetbook.Worksheets(I – 1).Cells(EndRow + 1, K) = Sourcebook.Worksheets(I).Cells(J, K)
        Next K
        EndRow = EndRow + 1
NextAsset:
    Next J
Next I
[COLOR="#008000"]'——————————————–
'SAVE AND CLOSE TARGET WORKBOOK[/COLOR]
Application.DisplayAlerts = False
Targetbook.Close savechanges:=True
Application.DisplayAlerts = True
End Sub

Reply | Quote

Maud,

Just a thought.

How about replacing this:

Code:

    'CHECK FOR DUPLICATES
        For M = 3 To EndRow
            If Sourcebook.Worksheets(I).Cells(J, 2) = Targetbook.Worksheets(I - 1).Cells(M, 2) Then GoTo NextAsset
        Next M
    '---------------------------------------------
    'COPY ASSET TO TARGET SHEET
        For K = 1 To col
            Targetbook.Worksheets(I - 1).Cells(EndRow + 1, K) = Sourcebook.Worksheets(I).Cells(J, K)
        Next K
        EndRow = EndRow + 1
NextAsset:

With this:

Code:

   Dim rngResults As Range

   Set rngResults = Targetbook.Worksheets(I - 1).Range(Cells(3, 13), Cells(lEndRow, 13)).Find(Sourcebook.Worksheets(I).Cells(J, 2), LookIn:=xlValues)
   
   If rngResults Is Nothing Then  '*** NOT Found ***
    'COPY ASSET TO TARGET SHEET
        For K = 1 To col
            Targetbook.Worksheets(I - 1).Cells(EndRow + 1, K) = Sourcebook.Worksheets(I).Cells(J, K)
        Next K
        EndRow = EndRow + 1
   End If

I think this code has 2 advantages:
1. The Find should be more efficient that the loop.
2. It gets rid of the GoTo.

Note: I tested the concept code but I have NOT tested the integration into your code!

HTH :cheers:

FYI: Here’s my test file: 37436-TestFindCode

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Well hello gentleman. Thank you for all of the help you have provided on this project. I have learned a great deal throughout this. Unfortunatly, I am back again with my hand out asking for help. I have two favors:

1. Can you look through this program and give me any suggestions as far as funtionality (or anything really)
2. As you can see there is a button on the start sheet that is labeled “Trends”. Ideally, I would like this to call the UFTrends userform and when the user clicks one of the buttons at the bottom, (this is where it gets difficult) it looks through the asset DATA sheet and the Air Compressor DATA sheet for any records of this asset and creats a line chart with a previous data. As you will see, the buttons on the bottom of the UFTrends userform match some of the column headers on the sheets. I have started working on it but I cant get anything to populate in the chart area. I am sure I am way off course.

P.S. the password to run “Application.visible=true” is bulldogs (click ADMIN button at top left)

Any direction you can give me would be wonderful!!!!

Jay

Reply | Quote

I moved this to a new thread as it did not pertain to the original post. I hope this is ok.

Reply | Quote