Saving Multiple Selected Worksheets From Listbox As PDF
Jun 25, 2014
To this point I have been able to successfully write code that will save a constant set of worksheets as a pdf. However, I would now like to alter it to be able to dynamically select the desired worksheets from a list box (I have been able to populate my list box) and then save as a pdf. The last step is where I am have issues. This is what I have thus far..
Dim relativePath As String
Dim Selected As Long
For Selected = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(Selected) = True Then
Sheets("Summary").Range("Q65536").End(xlUp)(2, 1) = ListBox1.List(Selected)
ListBox1.Selected(Selected) = False
[Code] ..........
View 6 Replies
ADVERTISEMENT
Feb 14, 2008
The following code populates a forms' second listbox (Elements_lbx) with True. No amount of diddling (by me) can get the selected items from form's first listbox (Content_lbx) moved over to the second. (NB. the list box isn't the standard VBA listbox but an open source: ListEX from Marco Bellinaso.)
Private Sub CommandButton2_Click()
Dim lbx_Sel As Long
' loops through ListBox to test if it is selected
For lbx_Sel = 0 To Content_lbx.ListCount - 1
If Content_lbx.Selected(lbx_Sel) = True Then
Me.Elements_lbx.AddItem Me.Content_lbx.Selected(lbx_Sel)
'
'Clear the selected item
Content_lbx.Selected(lbx_Sel) = False
End If
Next
End Sub
View 9 Replies
View Related
Aug 3, 2007
I am trying to find a easy way to delete multiple worksheets in a workbook and then saving the workbook based on the tab name of the worksheet.
View 9 Replies
View Related
Sep 26, 2006
changing two variables a various amount of times and running the same procedure and copying the resutls into another sheet. Seems like a perfect place for a macro. However, these variables can be chosen from a list that the user wants. So why not build in a listbox for each one. Now I have two listboxes one for variable A and one for variable B.
The procedure in theory goes something like this we change variable A from the base case and then run the procedure for variable B, get the results, then run the scenario again but changing only variable B abnd repeat. Then once, all of the variable B scenarios are done, I want to change the variable A and then repeat and so forth.
That is the background and my main problem at this point, is that have these values in two listboxes, I know how to do the for each loops and such, however, I do not know how to do them for values in the listbox.
How do I identify the values selected in the respective listboxes and then pull them so I only use them for the for each loop?
View 7 Replies
View Related
Nov 29, 2013
I have an excel file with over 20 worksheets and each of them have around 1200 rows. The first column in each worksheet contains the variable names and then the data associated to it is present horizontally.
I only want to keep around 80 rows from those 1200 rows. They are not in sequence (means they are not in continuous order) so I manually selected those rows by deleting the non required rows step by step.
I did it manually on 2 worksheets but I don't want to do that manually over 20 worksheets. Is there any method that can speedup the whole process.
I am attaching the snapshots of the worksheets..
This one is before I deleted the unwanted rows.
This one is after the deletion of unwanted rows.
View 3 Replies
View Related
Jul 25, 2014
I am using this code to hide or unhide rows of text on another sheet:
VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String
Dim sValue As String
'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)
[Code]....
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True
View 1 Replies
View Related
May 2, 2007
i have an excel table with embedded listboxes (Control Toolbar element). The user selects from listbox and has to send this file for approval. but when she saves the spreadsheet, the listbox-selection is reset to the first item. I know, that this is common for Excel, that the Listbox-selections are not saved. So I think the solution is:
1) to save the position of the shown listbox-item like
Cell(1,1).Value = Listbox1.TopIndex.Value
2) at reopening the saved file just to put this value into the Listbox like
Listbox1.TopIndex.Value = Cell (1,1).Value
But I don't know how to access this Control Toolbox objects from VBA. I've read some previous posts and then tried with
Worksheets("PAGE2"). OLEObjects("BC1L3_Isin").TopIndex.Value
but it doesn't work. The simplest solution would be to use the LinkedCell property, but the users do not always click on the ListBox to select the Item, they just scroll with the mouse and then save the file, so that ListIndex and TopIndex are not the same.
View 5 Replies
View Related
Jan 2, 2010
I currently have a tab on my spreadsheet named CSV. I have a macro that when called saves the information on that tab and sends it to a folder saved as a CSV file.
However this tab has a lot of information on and i want it so send it with only the information needed at the time. the data i want to send is in Columns A to AM and has rows down to 7392. I only want it to save the file with the data in the rows that has "Export" in Column AQ ignoring any other rows of data that i dont need it to send
I dont want anything to happen to the CSV tab ie delete lines etc as files will be getting sent regulary from that sheet still but with the data constantly updating in different cells
View 2 Replies
View Related
Jun 21, 2009
I have a worksheet in my workbook that contains data for 25 different airfoils, each one in a cell space of 3 columns and 50 rows.
In order to do some analysis on these airfoils, I need to save each 3x50 as separate text file to define input for analysis code.
How can I do this in VB in one macro?
View 9 Replies
View Related
Jul 17, 2014
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010
[URL]
View 1 Replies
View Related
Jul 17, 2014
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010.
I have tried hard to find other solutions with links at the following places but not exactly what I'm desiring:Auto lock cells after data entry when file saved...
Auto lock cells after data entered and SAVED.
Auto Lock Selected Range of Cells After Saving
I should also mention that most solutions either provide for locking/unlocking of all cells but not a selected range. Or they provide for locking/unlocking of a selected range but not for a specific sheet or not after you have saved the workbook.
This is unique in that I'd like it to autolock after i press the save button for a SPECIFIC number of cells. I just wanted to clarify as to not make others think that I haven't used the search function.
View 1 Replies
View Related
Nov 14, 2011
I want to add the selected value from listbox to textbox.
View 6 Replies
View Related
Dec 19, 2012
I have a spreadsheet with names of students, year groups, subjects and others. My VBA code will analyse student's results, but before I get there I would like the user to be able to select groups of students using listboxes.
Now I have a tonne of variables that I won't bore you with but the main thing I would like to do is to scan through each cell in a range on my spreadsheet and determine if the value of that cell is equal to a selected option in the listbox. This will let me, for example, filter by only Year 10 students, or select to display only Year 9 and 11 students.
I already have the code to scan through each cell:
VB:
'Run through each student and check they match the criteria
For N = 1 To StudentList.ListCount
'Split the first and second names
StudentName = Split(StudentList.List(N - 1))
[Code] .....
Essentially, I would like to take the cell "A" & N2 and check to see if it matches one of the selected options in a listbox called "YearList"
View 1 Replies
View Related
Apr 30, 2014
I have 1 macro that i would like to be activated as soon as i select any item from a form control listbox (doesn't matter which item). i am not using an active X control but rather a form control.
View 5 Replies
View Related
Dec 4, 2008
I can add an item from one list box to another using the following...
[Code].....
But I want to be able to remove the item from the listbox by clicking it. Tried this but doesn't work!
[Code] ......
View 10 Replies
View Related
Feb 27, 2014
I have a (MultiSelect) ListBox1 that is populated with Customer Names for the user to select from. I want the user to be able to hit the CommandButton1 adjacent to the ListBox1, and the selected Customers copy into/get added to the ListBox2.
I have the following code, adapted from other code, but it is not working:
[Code] ........
View 2 Replies
View Related
Dec 4, 2008
Sorry, should be a simple one...
I can add an item from one list box to another using the following...
View 7 Replies
View Related
Nov 21, 2011
I have a userform running with a number of text boxes, as well as one listbox. To extract a value from a text box I obviously write something = me.textbox.value. How do I do the same for a listbox (single item) as me.listbox.value produces an error!
View 1 Replies
View Related
Nov 20, 2012
I have a form control listbox, which is populated with filenames from a specific directory. When the user selects an entry I need that text to appear in a cell in the workbook. This is then concatenated with a folder path to open the file to copy and paste some data out into another workbook. I have tried...
Code:
Sub GetWrbkbkname()
Dim strlist As String
strlist = Sheet1.Listbox18.Text
Sheet1.Cells(1, 1) = strlist
End Sub
But receive the method or data member not found error. The cell link property just returns the position in the list! I need the text!!
The only workound I could think of was populating a space in the SS with the directory contents and using the cell link value in a choose function to return the selected workbook name.
View 9 Replies
View Related
Oct 25, 2013
I have a listbox on a userform, with multiselect set to 2 (Multiselect extended). When the listbox is displayed for the first time, no elements are selected. At a certain point in this application, I want to get back to the original state and deselect all items.
I do that with the statement Listbox1.listindex=-1. I hope that is the right thing to do. However, the elements that originally were selected are still blue-coloured, like they became when the user selected them. How do I do to make them look deselected?
It also seems to me that the array Listbox1.selected(x) still holds the value "true" for elements that were selected. Is it so that in addition to say listbox1.listindex=-1, you have to loop through the elements in listbox1 and set all of them to false?
View 2 Replies
View Related
Jun 23, 2014
I have a couple of listboxes and use the mouseup event to do stuff with the row that's clicked.
When a new listbox is clicked I'd like to remove the highlighting or selection from the last one.
I thought this would do it
".Selected(x)= true" where x would be that listbox's list index. But no.
Is there another way?
View 2 Replies
View Related
Jun 30, 2014
i would like to paste also the rows that selected from listbox i use a userform and also use multiselect listbox
Code:
Private Sub frmstartbtn_Click()
Dim lItem As Long
With Me.frmListBox1
[Code].....
View 1 Replies
View Related
Dec 1, 2006
i am making a phone company site on excel, with vba, for my college project, i need to know how to make it so wen i change the selected phone from the listbox, 2 show the image of the phone in the image box, from my files.
View 9 Replies
View Related
Nov 17, 2007
I have a workbook with roughly 25 sheets, each sheet represents a customer. Each month, I want to be able to run a macro in the workbook that will produce a UserForm containing a Listbox of each unhidden customer (worksheet) in the workbook. After I select all of some of the customers, hit a "process" button which will run a macro on each of the selected customers from the UserForm one worksheet at a time.
I currently have code written to produce the UserForm and populate the list, but I am uncertain how to write the code for the "process" button to run the macro on each selected customers one at a time. All I know how to do is have excel select all the chosen customer worksheets all at once.
View 8 Replies
View Related
Dec 5, 2007
I have a listbox with several items selected. Is there a way to automatically show the selected items in the worksheet (as values in cells)?
View 8 Replies
View Related
Nov 4, 2008
I am trying to e-mail a portion of a spreadsheet that I have. My approach is the following: I save a copy in a temporary folder, open that copy, remove all the sheets that I don't need to send out, save it (same name as the e-mail subject), and then e-mail as an attachment.
The current approach does not remove the macros and references though. I use the following to remove the macros and references:
View 3 Replies
View Related
Jul 22, 2007
I have this code (got it from here: [url] that saves worksheets as separate workbooks. It works perfectly fine for that but I would like to save each worksheet as a text file (tab delimited). I tried changing FileFormat:=xlNormal to FileFormat:=xlFile but a debug error occurs.
Sub MakeMultipleXLSfromWB()
'Split worksheets in current workbook into
' many separate workbooks D.McRitchie, 2004-06-12
'Close each module AND the VBE before running to save time
' provides a means of seeing how big sheets really are
'Hyperlinks and formulas pointing to other worksheets within
' the original workbook will usually be unuseable in the new workbooks.
Dim CurWkbook As Workbook
Dim wkSheet As Worksheet
Dim newWkbook As Workbook
Dim wkSheetName As String
Dim shtcnt(3) As Long
View 9 Replies
View Related
Jun 2, 2014
I have a listbox that is automatically filled with data in two columns through a lookup function from a worksheet. That works fine. But now I want to fill the third column with data through a text box, but only in the rows I have selected in the listbox.
My code so far is:
[Code] ....
The number I want to insert into the listbox appears, but not in column 3 in the selected row(s).
View 3 Replies
View Related
Aug 3, 2014
How to create simple macros assigned to button to extract the selected items from the list box to cells
Check the attached file : jjjj.xlsm
If I select y2 and y7....will copy these two selected to the next sheet "sheet2" but i want to paste it at the last used cell in column A
Additionally....code to reset the selected items from the listbox.
View 4 Replies
View Related
Mar 6, 2013
It seems that when reloading a listbox (in my case in ppt, but triggered from vba in excel) the ppt application (office 2010) was not highlighting the selected row in the listbox, and was infact also losing the selected row information when the item lost focus.
indication in excel that new data is to be displayed
triggers macro in ppt to fetch data and update:-
1. select the userform and listbox
2. fetch the data from excel worksheet range into a local array
3. save locally the current selected index in the listbox
4. reload the data into the listbox and adjust column widths
5. relocate the old selected item, and set the new selected index
Summary of key Code lines which did not work:
[Code] .....
Code which worked fine:
[Code] .....
So in summary, if you get problems with listbox selected items not being highlighted, double check you are not re-defining column widths after loading the data into the list.....
View 2 Replies
View Related
