Excel 2013 :: Not Loading Listbox With Sheet Names From Another Workbook
Feb 27, 2014
I've created a macro that loads a listbox with the sheet names from another workbook. It works fine with Excel 2007-2010 but some reason fails with 2013. It doesn't crash, it just doesn't add items to the listbox nor doest it add the caption to Userform1.Caption. It does launch the form. Here is the code snippet:
Code:
'open read-only
wbkpath = Sheet3.Cells(1, "f") 'full pathway to source workbook
Workbooks.Open Filename:=wbkpath, ReadOnly:=True
Set swbk = ActiveWorkbook
swbk.Activate
[Code] ...........
View 2 Replies
ADVERTISEMENT
Feb 13, 2014
I was given a spread sheet that prepares a file for export to some ones system. The macro in the spread sheet wouldn't run as I'm using excel 2013 which when you open a new work book it only opens sheet 1 and there was a bit in the macro that wanted to send information to sheet 2 which wasn't there. I added a bit of code which created sheet 2 and hey presto it worked fine. I made the mistake of telling the people who supplied the original workbook to me what I had done so that they could pass the modified sheet on to other users in case they had the same problem.
Now I've been asked if I could do a bit more work to the existing macro so that instead of the new workbook just being called Book 1 could I add a name and date to the new workbook being created.
I've had a look at the original code and it looks like it would be quicker to start from scratch as I cant follow the original code.
The existing workbook has 3 sheets, I want to copy sheet2 to the new workbook. I want to name the new workbook as "a fixed name" with the "time and date " .xlsx
View 9 Replies
View Related
Mar 25, 2014
I have a workbook with five sheets. One sheet is the Master and then one sheet each for four organizations. Each organization will populate data for their own sheet and I want Excel to automatically populate the Master with the info from each individual sheet.
I tried using and modifying the vba from this post [URL]). My workbook varies from the original in that the first seven rows are header rows so the first cell to be edited in each sheet is A8, I have 38 columns instead of 10, and I am using Excel 2013 instead of Excel 2003.
I figured out that I needed to change the vba to look like this to start copying from the first cell (A8) and to populate the master:
This seems to work, but until the first cell (A8) is populated in the organizations' sheets, it messes up the prior row on the Master (Row 7).
I've also noticed that undo becomes unavailable when switching between sheets and also copy/cut & paste between sheets doesn't work so I can't correct the way the script messes up the format initially (this I presume is due to the Copy Destination in the vba?).
View 1 Replies
View Related
Jan 27, 2014
We have had a few instances where Excel 2013 is truncating a filename when saving for example "MF Not Settled - Dec 13 Rental A.." the filename includes additional characters than this but has replaced them with .., I initially thought it could be a file path length issue but the files in question the total length is less than 150 characters including the server name.
This is also happening with links inside excel files to different tabs in the same workbook and well as macro names causing them to have to be ran manually as it has truncated the name to .. at the end.
find some examples below:
Full filename: MF Jan 2014 vols Smart churn sensitivity
Truncated filename: MF Jan 2014 vols Smart churn...
Also if you try to link to the truncated workbook whilst open it displays the formula as:
='[MF Jan 2014 vols Smart churn...sitivity.xlsm]Input c!$E$13
My work around is to save the file to another folder, without closing it, and then it works fine. However, in doing this it will try to save as the truncated name, so I first have to select the correct name of the file in the existing folder, before saving to a separate folder. However, if the file is closed, and then reopened the problem returns.
View 2 Replies
View Related
Jul 31, 2014
I am trying to rename a chart in Excel 2013, but it keeps reverting my names. It looks like there might be a 32 character limit on the name length, but I cannot find any documentation supporting this.
In case it's not clear, I'm trying to rename a chart from Chart38 to "Productivity - Tractors CY/OP - Month", but once I press Enter, it changes back to Chart38. If I can shorten the name to less than 32 chars it seems to work, but that will be nearly impossible for some other charts.
View 3 Replies
View Related
Jun 26, 2014
I have a userform, a textbox and a listbox.
I want to populate the listbox dependant on the worksheet names, skipping the first 4 worksheets The texbox should highlite a value in the listbox (if exists).
View 2 Replies
View Related
Dec 21, 2011
My set up office 2007 win xp pro ie8
is it possible using vba ideally with a button marked save, to save a single worksheet and name it, in CSV format
Then I would like to be able to do the reverse load a CSV file back in to a work sheet
Expanding upon this
1. Click a command button with caption “save”
2. Take the name of a given sheet, in my case DataToUpLoad and append it with a number to give a sheet name of DataToUpLoad 1 the first time it is save and DataToUpLoad 2 the next time and so on
3. Click another command button with the caption “load” interact with usual dialog box.
My current work around is to copy a sheet open a new work book past the sheet in the save that work book as CSV
The reason this is required is to mate up with the php on the server side
View 3 Replies
View Related
Jan 6, 2014
I'm trying to simplify a spreadsheet i've been given by defining names for certain values. I'm using Excel 2013. Is there any way to have all of the newly defined names I've created for cells automatically be inserted into all of the formulas that exist in the spreadsheet?
Example.
Old formula in one sheet of a workbook looks up a cell in another sheet with the value of ='sheet1!A1
I've given the value of cell A1 in Sheet1 a name of dgwd.
How do I get every formula in the workbook that references 'sheet1!A1 to change the value within that formula to dgwd?
View 4 Replies
View Related
Jan 23, 2013
How can I fill a Listbox on a worksheet with all the work sheet names in a workbook. If one is added it will update the listbox, not using a userform. All the sheets will be hidden except main sheet with listbox?
View 8 Replies
View Related
Dec 21, 2006
iam trying to populate a listbox with only the workbooks hidden sheets. iam trying to adapt this code which i found in the forums
Private Sub UserForm_Initialize()
Dim wsSheet As Worksheet
Dim lngIndex As Long
With ThisWorkbook
Redim strarray(.Worksheets.Count - 1, 1) As String
lngIndex = 0
For Each wsSheet In .Worksheets
strarray(lngIndex, 0) = wsSheet. Name
lngIndex = lngIndex + 1
Next
End With
With ListBox1
.List = strarray
End With
End Sub
View 7 Replies
View Related
May 25, 2007
I have created a userform in excel with a listbox that has the names of 4 different sheets located within that workbook. I would like for whenever a name of the sheet is highlighted in the listbox, that sheet is opened. I have played around with the listbox.value and if then statements and can't seem to get it to work.
View 2 Replies
View Related
Feb 25, 2011
Is it possible to produce a list on a new worksheet of all sheet names and their their internal names within a workbook?
If so I would like the tabbed name's to begin in say A2 with the corresponding internal name in B2.
View 3 Replies
View Related
Dec 26, 2012
How to get a listbox to populate from a database sheet the code I am using to do this is posted here and it works great. My problem is now I want to get the Listbox1 results to populate my UserForm5 textboxes from the row selected in the ListBox1 of Userform4, but I am getting the error "variable not defined" and the "rw" gets highlighted. as seen in the code below it.
UserForm4 portion of the code that does the search of the sheet that holds the data
Code:
Private Sub CommandButton1_Click()
Dim sFindText As String, sFirstAddr As String
Dim i As Long, lFindCol As Long, lCol As Long, lRow As Long
Dim rMyData As Range, rMySearchField As Range, cFound As Range
Dim vArray() As Variant
[Code] ......
Userform5 issue is this rw in red doesn't seam to be right
Code:
Private Sub UserForm_Initialize()
TextBox1.Value = SrcWks.Range(UserForm4.ListBox1.List(rw, 0)).Offset(0, 0) '
TextBox2.Value = SrcWks.Range(UserForm4.ListBox1.List(rw, 0)).Offset(0, 1) '
TextBox3.Value = SrcWks.Range(UserForm4.ListBox1.List(rw, 0)).Offset(0, 2)
TextBox5.Value = SrcWks.Range(UserForm4.ListBox1.List(rw, 0)).Offset(0, 3)
[Code] .........
View 9 Replies
View Related
Feb 20, 2014
I work for one half of a joint venture & am responsible for planning & expediting. The other half does purchasing. The bi-weekly PO download reports I receive are less than useful. I have already written the code to delete undesired sheets & add, format, and enter headers for a "Summary" sheet.
I need code to move to the next row, and run formulas to pull data from the next sheets, and repeat until there are no more sheets.
The number of sheets will vary from one download to the next, and the sheet names will vary from one download to the next.
Following are example formulas that need to be run on successive rows while pulling from successive sheets.
I am running Excel 2013 on Windows 8.1
View 14 Replies
View Related
Dec 18, 2013
Tried a macro with no luck, I want to save workbook to PDF and have it named by the value in A3. I want the Dialog box to pop up so I pick the folder?
View 6 Replies
View Related
Dec 4, 2008
I have this script (below, Krishnakumar orignally provided this script).
I'm trying to edit it to add cell values from cells C16 and E16 along with the worksheet name it displays in listbox2. So lets say worksheet "rollover" is the sheet being displayed in the listbox2.
I would like for it to look something like this:
rollover TT:'data from cell C16' TA:'data from cell E16'
Public FilePath As String
Public dic As Object
Public oWB As String
Public oWS As String
Public aWS As Worksheet
Private Sub CommandButton1_Click()
Dim i As Long, wb As Workbook, n As Long
With Me.ListBox2
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
oWS = .list(i)
Set wb = Workbooks.Open(FilePath & oWB, UpdateLinks:=0)
wb.Sheets(oWS).Activate
Exit For
End If
Next
End With
End Sub
View 7 Replies
View Related
Feb 22, 2008
I have a spreadsheet containing a list of key fob numbers, key numbers, etc. I have 3 userforms with different for different options, i.e. Key fob no. search, key number search, room number search. I have set up the code for each of the userforms to search for the information entered in the textbox and then lists the info in the listbox which I can then click on the one of the listed items to take me directly to the place in the spreadsheet.
However since there are a range of different key numbers per key fob, I would like to know what code I can use to allow a range of information such as the key number and room number, etc to be displayed in the same listbox as the Key fob number i searched for.
Here is my code for one of the Userforms:
Option Explicit
Sub Locate(Name As String, Data As Range)
Dim rngFind As Range
Dim strFirstFind As String
With Sheet1.UsedRange
Set rngFind = .Find(Name, LookIn:=xlValues, lookat:=xlPart)
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address
Do
I cannot attach a example of my form since the site seems to have a limit on the size of the upload and my part of the file compressed in zip format is still at 168kb.
View 5 Replies
View Related
Oct 15, 2013
Code:
Sheets(Array("Sheet 1", "Sheet 2")).Visible = False
How do I convert the above to using Sheet Codes Names, Sheet1 and Sheet2?
Want to ensure my code will work if the user changes the sheet name.
View 2 Replies
View Related
Sep 22, 2007
A custom UserForm pops up when a cell is double-clicked. The form contains a ListBox that presents the user with a list of values to select. When the UserForm first pops up I want the item that matches the value of the activecell to be highlighted/selected.
View 8 Replies
View Related
Mar 1, 2014
I'm trying to print an excel workbook in a particular format. I have several sheets. I would like the information on each sheet to be duplicated directly to the right of the info, on the same printed sheet in Landscape layout. After printing, I need to be able to stack the workbook and cut straight down the middle. This way, I have two identical workbooks with identical margins, perfectly centered.
Is there an easy way to do this without copying and pasting the cell data into the spreadsheet as well?
If there is not, and I must have duplicate tables on the same worksheet, is there an easy way to format the printing so that they print out perfectly aligned?
I've tried to use the ruler function in the print page to stretch, but, it only stretches a particular row/column.
View 1 Replies
View Related
Jul 21, 2014
How do I copy a colour scheme from another work book in Excel 2013? I could do it in 2010 but can not work out how to do it in 2013..
View 4 Replies
View Related
Aug 7, 2013
With excel 2013 you're now able to open multiple windows or views of the same workbook. However when a workbook is saved with multiple windows open, the next user to open the spreadsheet will also open it with multiple windows. Which can be very annoying when most people work off of one window vs. multiple. Is there a way to disable saving the multiple windows or a macro to force open excel in 1 window?
View 2 Replies
View Related
Jun 23, 2014
I've got a spreadsheet that pulls data from a SQL server.
I'm looking to have a button on the first sheet that extracts the data from SQL and inserts it into sheet 2 of spreadsheet. The data connection from SqL is working fine but when I click on the button sheet 2 is blank.
View 1 Replies
View Related
Sep 6, 2013
In the first sheet called WSZYSTKIE (All) I input new invoices with the deadline for payment. Everyday I'm looking for invoices that I have to pay today. Dates with deadline are one column(E), dates when I paid is another one(F). I'm wondering if it is possible to do following thing:
After opening file, this would be done automatically: If there is invoice (row) to be paid in next 7 days (great if I could choose what time range I'm interested with), copy entire row to the second sheet called: Do zapłacenia (TO PAY). If there is invoice with deadline which is i.e. 2 days ago, copy whole row to same sheet and mark it RED. If it will be paid, I'm entering date at which I've paid and it should be moved to the next sheet called: Archiwum(PAID), and this row in sheet Wszystkie(ALL_ should be actualized with the date I made payment.
Excel 2013, but finally it will be used on excel 2007.
View 7 Replies
View Related
May 9, 2014
I'm using Excel 2013 on Windows 7.
I have an Excel file which has a macro that sucks in data from a number of separate files (two Excel, a varying number of csv). The macro grabs all the individual files and loads them into one of three worksheets with some formatting, sorting, and structuring on the way. All good so far.
I then need to take two of the worksheets and copy them to a new Workbook which I can send out to some users. So I use the Workbooks.Add method, and use the Selection.Copy on just the data (UsedRange.Rows/Columns.Count to ensure it is only the data) and Selection.PasteSpecial with the xlPasteColumnWidths, xlPasteValues, and xlPasteFormats options to copy the data over. None of the data is filtered. Just straight data.
I would expect the newly added workbook to be smaller than the one with the macros because the one with the macros has two extra sheets that I don't transfer, and the sheets I do transfer are identical.
And that's where it gets weird. The file with the macros and more data is 18.7Mb, but the new one with only two sheets is 24.8Mb. One is .xlsm and the other is .xlsx (because it has no macros).
I've tried opening the new workbook and saving as .xlsm (no material difference to the size) and as .xls (it got even bigger). I've opened the new workbook, gone to the end of the data and deleted all the blank rows and columns, but no effect (because the rows are already empty).
Why would the file with less in it be so much bigger (or bigger at all)?
View 2 Replies
View Related
Jan 27, 2014
I'm using some workbook-scoped named formulas to define some dynamic ranges which will be referred to by numerous worksheets. The named ranges are defined like:
NAME: gTable_costDetailsEquipment
REFERS TO: =globalParameters!$B$5:INDEX(globalParameters!$B$5:$C$1048576,1+countAdjacentNonBlank
(globalParameters!$B$5,"down"),1+countAdjacentNonBlank(globalParameters!$B$5,"right"))
From either of the tabs "Reports" or "DOR_Template" the user can press the large "+" icon to add a report (which copies the template or the last report to a new sheet).
When this Sheet copy takes place, excel is repeating my named formulas - this time it's making LOCAL versions scoped to the newly made worksheet.
I've used this copy sheet trick before and have never had excel create new, locally scoped, named formula for each workbook level name.
I also just recently started using excel 2013, is this a problem with the new version? I've just never seen this problem, usually workbook-level names are NOT duplicated on sheet copy.
View 2 Replies
View Related
Aug 12, 2014
I am gaining an error in my code due to the sheet being protected.
I cannot seem to get the correct code to allow for the code to still run, while the sheet is protected.
[Code] .....
I tried protecting the sheet via:
[Code] ....
But I still get an error.
View 2 Replies
View Related
May 11, 2014
We are doing a graduation project on an international airport, consist of scheduling flights on check-in counters automatically.
long story short, we ended up with an excel sheet like this: Screen_Shot_2014-05-11_at_4.png
Were y-axis are the check-in counters and x-axis is the timeline horizon (cell per 5-minutes)
I will do a VLOOKUP, to change each flight number to it's ID from an other sheet.
But the problem is that i want to AUTO-MERGE all cells with same value, because they represent one flight! and if i shortened the column width i can't see anything.
I want it to be like this: (i've done this manually, and it's VERY time consuming with errors because we have to do it for all days.)
Screen_Shot_2014-05-11_at_46FDQO.png
I googled for days, i only found Visual basic commands i guess? that only merge same rows. and they were poorly made. beside that it didn't work properly. Method to do it automatically?
EXCEL 2013
View 3 Replies
View Related
May 11, 2014
We are doing a graduation project on an international airport, consist of scheduling flights on check-in counters automatically.
Excel sheet like this:
Were y-axis are the check-in counters and x-axis is the timeline horizon (cell per 5-minutes)
I will do a VLOOKUP, to change each flight number to it's ID from an other sheet.
But the problem is that i want to AUTO-MERGE all cells with same value, because they represent one flight! and if i shortened the column width i can't see anything.
I want it to be like this: (i've done this manually, and it's VERY time consuming with errors because we have to do it for all days.) Any method to do it automatically?
EXCEL 2013
View 3 Replies
View Related
Jul 20, 2014
I've created a chart in sheet 1 in a workbook. I want to copy and paste that chart into another sheet (lets take sheet 2) of the same workbook. I am using Excel 2010 version.
Whenever, I try to copy a graph and want to do "Paste Special as Link picture". The problem I am facing as "Paste Link" option is inactive.
I am attaching the Excel for your reference.
View 2 Replies
View Related
