Jump to content

JPL

Quark
  • Content count

    18
  • Joined

  • Last visited

Community Reputation

0 Serf

About JPL

  • Rank
    Initiate
  1. JPL

    Excel Multiple TextBoxs

    Thanks Galdarian, i'll give your example ago. Yep you are correct, it's listbox not textbox. Basically i have userform with a listbox linked to columns on the Prefill_Values where the columns are titled by names and contain various numbers below them. The userform also has Multiple pages so when i click on a name in the listbox, the information under their names on the Prefill_Values sheet pops into the combobox on the relevant multipages. The columns titled by name are duplicates with different info under each so the info under one name pops into one combobox on one multipage and the same name with different info pops into another combobox on a different multipage. Hope that's not to confusing.
  2. 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?
  3. JPL

    Excell Pivot Table Source

    Thanks galdarian; that worked a treat. Thanks again for your help.
  4. JPL

    Excell Pivot Table Source

    Thanks galdarian that seems to have worked but it then brings up the name of the work book, the sheet name and the cell range of source data. Can it be modified so that it only brings up the sheet name?
  5. 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?
  6. JPL

    Excel Macro

    I'll give that ago & see what happens.. Cheers..
  7. JPL

    Excel Macro

    When clicking on the copy'd command button it goes into debug mode and highlights the NewZip (FileNameZip) under 'Create empty Zip File but also says Compile error Sub or Function not defined.
  8. JPL

    Excel Macro

    It's only one workbook but contains multiple spreadsheets..
  9. JPL

    Excel Macro

    I have attached the code below. The section that appears to be incorrect is under 'Create empty Zip File - NewZip (FileNameZip) Dim strDate As String, DefPath As String, strbody As String Dim oApp As Object, OutApp As Object, OutMail As Object Dim FileNameZip, FileNameXls Dim FileExtStr As String DefPath = Application.DefaultFilePath If Right(DefPath, 1) <> "\" Then DefPath = DefPath & "\" End If 'Create date/time string and the temporary xl* and zip file name If Val(Application.Version) < 12 Then FileExtStr = ".xls" Else Select Case ActiveWorkbook.FileFormat Case 51: FileExtStr = ".xlsx" Case 52: FileExtStr = ".xlsm" Case 56: FileExtStr = ".xls" Case 50: FileExtStr = ".xlsb" Case Else: FileExtStr = "notknown" End Select If FileExtStr = "notknown" Then MsgBox "Sorry unknown file format" Exit Sub End If End If strDate = Format(Now, " yyyy-mm-dd") FileNameZip = DefPath & Left(ActiveWorkbook.Name, _ Len(ActiveWorkbook.Name) - Len(FileExtStr)) & strDate & ".zip" FileNameXls = DefPath & Left(ActiveWorkbook.Name, _ Len(ActiveWorkbook.Name) - Len(FileExtStr)) & strDate & FileExtStr If Dir(FileNameZip) = "" And Dir(FileNameXls) = "" Then 'Make copy of the activeworkbook ActiveWorkbook.SaveCopyAs FileNameXls 'Create empty Zip File NewZip (FileNameZip) 'Copy the file in the compressed folder Set oApp = CreateObject("Shell.Application") oApp.Namespace(FileNameZip).CopyHere FileNameXls 'Keep script waiting until Compressing is done On Error Resume Next Do Until oApp.Namespace(FileNameZip).items.Count = 1 Application.Wait (Now + TimeValue("0:00:01")) Loop On Error GoTo 0 'Create the mail Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) strbody = "" & vbNewLine & vbNewLine & _ On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "" .Body = strbody .Attachments.Add FileNameZip .Send 'or use .Display End With On Error GoTo 0 'Delete the temporary Excel file and Zip file you send Kill FileNameZip Kill FileNameXls Else MsgBox "FileNameZip or/and FileNameXls exist" End If End Sub
  10. JPL

    Excel Macro

    A little help required. I have an excel workbook containing multiple spreadsheet. I have a macro which will allow me to zip the workbook and send it as an e-mail. The macro is only on one of the spreadsheets and I want to place that macro on all the spreadsheets but if I copy & paste it or just add another command button on the other sheets & type in the code, it won't work. Any ideas?
  11. JPL

    Excel Formulas

    Thanks. I did a bit of reading & searching and you can also use an array. The FREQUENCY function in an array will compare the numbers in a grid to a specific number in a legend. Either way as i will need multiple cells with the COUNTIF formula in it, ie: "=3", "=4" to pick at the numbers so shall it also be the case with FREQUENCY.
  12. JPL

    Excel Formulas

    Thanks for that. Its work well when looking for a specific number & how many time it repeats. I'll just re-arrange the spreadsheet with the code spread across the top row to look for different numbers in the grid. Thanks again.
  13. JPL

    Excel Formulas

    hi all, just working on averages in excel. the spreadsheet is just of multiple numbers & i'm trying to work out the averages. not the averages of certain numbers but of how many times the same number appears. is this possible?
  14. JPL

    Hotmail

    i gave it another go, downloaded Flash 9 & tried it with ie6 & 7, then downloaded FF3 & each time i go to log on at Hotmail i still get the same result/fault, upgrade the browser. even though i actually get on, once i try to check the e-mails it tosses me out.
  15. JPL

    Hotmail

    thanks for the info. i'll try the install Flash 9 and see how it goes, then re-try the ie6 or ie7. firefox, i also find a better way to go but i'm using ff3 at work & it's so slow starting up, but my work pc is crap so that could explain it.
×