Jump to content


JPL

Member Since 10 Sep 2008
Offline Last Active Jul 04 2017 04:01 PM
-----

Topics I've Started

Excel Multiple TextBoxs

30 June 2017 - 06:29 PM

Hi ya, just wondering if anyone could help. I've created a user-form with multiple textboxes. One textbox has a listed names and when clicking on one name i want to get another textbox reflect information detailed on sheet2 (which is called Prefill_Values) of the excel workbook. I seem to have it slightly working. When i click on the first name the second textbox details the info but there's another 3rd textbox which should reflect different info but shows the same info as the 2nd. 

 

  'Fill TeamListBox (only needs to be done once)
    With TeamListBox
        .AddItem "Name"
        .AddItem "Name"
        .AddItem "Name"
    End With

    form_filled = False

End Sub

 

Private Sub TeamListBox_Click()
'This function runs when one of the options in the team list box is selected.
'It prefills the form with the parameters for that team, using the prefill_team function, with syntax
    Dim column As Integer
    
    If TeamListBox.Value = "Name" Then
        column = 6
    ElseIf TeamListBox.Value = "Name" Then
        column = 7
    ElseIf TeamListBox.Value = "Name" Then
        column = 8
    Else
        column = 9
        
    End If
    
    For ii = 1 To NumberStaffCat
        Me.Controls("StaffNumbers" & CStr(ii) & "SpinButton") = Sheets("Prefill_Values").Cells(ii + 1, column)
        Me.Controls("StaffNumbers" & CStr(ii) & "TextBox") = Sheets("Prefill_Values").Cells(ii + 1, column)
    Next ii
    
    For ii = 1 To NumberFleetItems
        Me.Controls("FleetNumbers" & CStr(ii) & "SpinButton") = Sheets("Prefill_Values").Cells(ii + 1, column)
        Me.Controls("FleetNumbers" & CStr(ii) & "TextBox") = Sheets("Prefill_Values").Cells(ii + 1, column)
    Next ii
    
    form_filled = True

End Sub

 

The info on Sheet2 is spread over 20 columns but i need to pick out specific one.

 

anyone have any ideas i could use?


Excell Pivot Table Source

28 April 2017 - 10:35 AM

Hello, quick question regarding Excell pivot tables and source data. I have a pivot table on one spreadsheet and multiple spreadsheets containing the source date.

I have used the following =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))) which gives me the tab name of the spreadsheet the pivot table is on.

But is there a way that in another cell it's able to show the name of the spreadsheet the source data is on; and change as I switch between source data spreadsheets?