Search Results

Search found 435 results on 18 pages for 'workbook'.

Page 6/18 | < Previous Page | 2 3 4 5 6 7 8 9 10 11 12 13  | Next Page >

  • Using VBA / Macro to highlight changes in excel

    - by Zaj
    I have a spread sheet that I send out to various locations to have information on it updated and then sent back to me. However, I had to put validation and lock the cells to force users to input accurate information. Then I can to use VBA to disable the work around of cut copy and paste functions. And additionally I inserted a VBA function to force users to open the excel file in Macros. Now I'm trying to track the changes so that I know what was updated when I recieve the sheet back. However everytime i do this I get an error when someone savesthe document and randomly it will lock me out of the document completely. I have my code pasted below, can some one help me create code in the VBA forum to highlight changes instead of through excel's share/track changes option? ThisWorkbook (Code): Option Explicit Const WelcomePage = "Macros" Private Sub Workbook_BeforeClose(Cancel As Boolean) Call ToggleCutCopyAndPaste(True) 'Turn off events to prevent unwanted loops Application.EnableEvents = False 'Evaluate if workbook is saved and emulate default propmts With ThisWorkbook If Not .Saved Then Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _ vbYesNoCancel + vbExclamation) Case Is = vbYes 'Call customized save routine Call CustomSave Case Is = vbNo 'Do not save Case Is = vbCancel 'Set up procedure to cancel close Cancel = True End Select End If 'If Cancel was clicked, turn events back on and cancel close, 'otherwise close the workbook without saving further changes If Not Cancel = True Then .Saved = True Application.EnableEvents = True .Close savechanges:=False Else Application.EnableEvents = True End If End With End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Turn off events to prevent unwanted loops Application.EnableEvents = False 'Call customized save routine and set workbook's saved property to true '(To cancel regular saving) Call CustomSave(SaveAsUI) Cancel = True 'Turn events back on an set saved property to true Application.EnableEvents = True ThisWorkbook.Saved = True End Sub Private Sub Workbook_Open() Call ToggleCutCopyAndPaste(False) 'Unhide all worksheets Application.ScreenUpdating = False Call ShowAllSheets Application.ScreenUpdating = True End Sub Private Sub CustomSave(Optional SaveAs As Boolean) Dim ws As Worksheet, aWs As Worksheet, newFname As String 'Turn off screen flashing Application.ScreenUpdating = False 'Record active worksheet Set aWs = ActiveSheet 'Hide all sheets Call HideAllSheets 'Save workbook directly or prompt for saveas filename If SaveAs = True Then newFname = Application.GetSaveAsFilename( _ fileFilter:="Excel Files (*.xls), *.xls") If Not newFname = "False" Then ThisWorkbook.SaveAs newFname Else ThisWorkbook.Save End If 'Restore file to where user was Call ShowAllSheets aWs.Activate 'Restore screen updates Application.ScreenUpdating = True End Sub Private Sub HideAllSheets() 'Hide all worksheets except the macro welcome page Dim ws As Worksheet Worksheets(WelcomePage).Visible = xlSheetVisible For Each ws In ThisWorkbook.Worksheets If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden Next ws Worksheets(WelcomePage).Activate End Sub Private Sub ShowAllSheets() 'Show all worksheets except the macro welcome page Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible Next ws Worksheets(WelcomePage).Visible = xlSheetVeryHidden End Sub Private Sub Workbook_Activate() Call ToggleCutCopyAndPaste(False) End Sub Private Sub Workbook_Deactivate() Call ToggleCutCopyAndPaste(True) End Sub This is in my ModuleCode: Option Explicit Sub ToggleCutCopyAndPaste(Allow As Boolean) 'Activate/deactivate cut, copy, paste and pastespecial menu items Call EnableMenuItem(21, Allow) ' cut Call EnableMenuItem(19, Allow) ' copy Call EnableMenuItem(22, Allow) ' paste Call EnableMenuItem(755, Allow) ' pastespecial 'Activate/deactivate drag and drop ability Application.CellDragAndDrop = Allow 'Activate/deactivate cut, copy, paste and pastespecial shortcut keys With Application Select Case Allow Case Is = False .OnKey "^c", "CutCopyPasteDisabled" .OnKey "^v", "CutCopyPasteDisabled" .OnKey "^x", "CutCopyPasteDisabled" .OnKey "+{DEL}", "CutCopyPasteDisabled" .OnKey "^{INSERT}", "CutCopyPasteDisabled" Case Is = True .OnKey "^c" .OnKey "^v" .OnKey "^x" .OnKey "+{DEL}" .OnKey "^{INSERT}" End Select End With End Sub Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean) 'Activate/Deactivate specific menu item Dim cBar As CommandBar Dim cBarCtrl As CommandBarControl For Each cBar In Application.CommandBars If cBar.Name <> "Clipboard" Then Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True) If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled End If Next End Sub Sub CutCopyPasteDisabled() 'Inform user that the functions have been disabled MsgBox " Cutting, copying and pasting have been disabled in this workbook. Please hard key in data. " End Sub

    Read the article

  • Getting Run time 1004 error in code

    - by krishna123
    I tried the code provided by vba express for combining sheet, while execution it is displaying Run Time error 1004: Application Defined or Object Defined Error: My Scenario is: I have a Excel, in that I have first sheet "Connection" and after it I have Sheet1, Sheet2 and so on. I am combining all sheets except Sheet"Conection" by saying start with sheet2. I tried following line of code to exclude "Connection" sheet: If Not Sheet.Name = "Connection" then but it did not work. Whatever the sheets I have in some of them I have large data in some cells. Here is the code which I am using: I have highlighted the line Sub CopyFromWorksheets() Dim wrk As Workbook 'Workbook object - Always good to work with object variables Dim sht As Worksheet 'Object for handling worksheets in loop Dim trg As Worksheet 'Master Worksheet Dim rng As Range 'Range object Dim colCount As Integer 'Column count in tables in the worksheets Set wrk = ActiveWorkbook 'Working in active workbook For Each sht In wrk.Worksheets If sht.Name = "Master" Then sht.Delete Exit Sub End If Next sht 'We don't want screen updating Application.ScreenUpdating = False 'trg.SaveAs "C:\temp\CPReport1.xls" 'Add new worksheet as the last worksheet Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count)) 'Rename the new worksheet trg.Name = "Master" 'Get column headers from the first worksheet 'Column count first Set sht = wrk.Worksheets(2) colCount = sht.Cells(1, 255).End(xlToLeft).Column 'Now retrieve headers, no copy&paste needed With trg.Cells(1, 1).Resize(1, colCount) .Value = sht.Cells(1, 1).Resize(1, colCount).Value 'Set font as bold .Font.Bold = True End With trg.SaveAs "C:\temp\CPReport1.xls" 'We can start loop 'Skip Sheet - Connection If Not sht.Name = "Connection" Then For Each sht In wrk.Worksheets 'If worksheet in loop is the last one, stop execution (it is Master worksheet) If sht.Index = wrk.Worksheets.Count Then Exit For End If 'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount)) 'Put data into the Master worksheet '----------------- Error in below line -------------------------------------------------- trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value '---------------------------------------------------------------------------------------- Next sht End If 'Fit the columns in Master worksheet trg.Columns.AutoFit 'Dim dest, destyfile 'dest = "E:\Test_Merge\" 'destyfile = dest & "_" & trg.Name 'trg.SaveAs (destyfile) 'Screen updating should be activated Application.ScreenUpdating = True End Sub

    Read the article

  • POI performance

    - by The Machine
    I am using POI in my J2EE web application to generate a workbook. However, i find that POI takes around 3 mins to create a workbook with 25K rows(with around 15 columns each). Is this a POI performance issue , or is it justified to take that much of time? Are there other APIs known for better performance ?

    Read the article

  • Excel password removal

    - by nkcooke
    We receive Excel workbook files every day which are password protected with the same password. We know this password. Is there a utility or method to remove password protection on these workbook files without invoking Excel.exe or the Excel object. Our goal is to take Excel out of the process and utilize SpreadsheetGear in VB.net. However, SpreadsheetGear can only unprotect worksheets not workbooks. Thanks

    Read the article

  • How do I delete a signature from an Excel document using Office automation

    - by Guy Marom
    Hello, I have a signed Excel workbook and I want to delete the signature from it. The problem is that when I try to delete the signature there's a prompt for confirming the deletion and I want the process to be fully automatic. Here's a code sample, the prompt appears when the last line executes: Dim source As String = "c:\temp\signed.xlsx" Dim app As New Application() app.Visible = True Dim book As Workbook = app.Workbooks.Open(source, UpdateLinks:=0) app.ShowToolTips = False Dim sig As Microsoft.Office.Core.Signature = book.Signatures.Item(1) sig.Delete() Thanks

    Read the article

  • ruby parseexecel gem - array not implemented

    - by josh
    I am trying to work with two worksheets at the same time. So I have code require 'parseexcel' #Open the excel file passed in from the commandline workbook = Spreadsheet::ParseExcel.parse(ARGV[0]) workbook2 = Spreadsheet::ParseExcel.parse(ARGV[1]) #Get the first worksheet worksheet = workbook.worksheet(0) worksheet2 = workbook2.worksheet(0) However, when I run this code I get an error: array is not implemented This error goes away when I comment out line: workbook2 = Spreadsheet::ParseExcel.parse(ARGV[1]) Why is this happeneing? Way I am running script is: ruby -rubygems traverse.rb excel.xls so.xls

    Read the article

  • Excel: Make conditional formatting static

    - by Martin
    Is there any way to convert conditional formatting to static formatting in Excel? I'm trying to export a range of a Excel Sheet to a new Workbook, with identical appearance but no formulas, links, etc. The problem here is that I have conditional formatting that relies on calculations outside exported range. I've tried saving the workbook to .html, oddly enough the formatting shows in IE but not when reopening it in Excel.

    Read the article

  • How to create a workboook specific Excel Add in

    - by Ankit
    I would like to create a excel Add in which creates some additional toolbars and Menu buttons. But I want this addin to load only when a specific workbook is opened. I dont want to load the Addin if anyother workbook is open. I would like to know what are the possible ways to solve this problem and what is the best approach to implement this Add in (XLA or VSTO or COM Addin)

    Read the article

  • Excel Save fails in Windows 7

    - by Karthik
    Hi, I have created an application to interact with Excel in C#.net The API to save the Excel Excel._Workbook m_oWorkBook; m_oWorkBook.Save(); Throws up a File Saveas MessageBox though the workbook was already saved in a given name. Note: This happens only in Windows 7 & only when another Workbook was already opened. Any clues.

    Read the article

  • Starting Off VBA Macro With C# - Password Grief

    - by ChloeRadshaw
    I am stuck - I have a macro in a workbook which I can call quite easily using the run method on MSDN The problem is that if the workbook has a password protecting the code then this becomes much more difficult and forces a dialog box to appear.. Does anyone know how to run a macro from C# which has a password on it??

    Read the article

  • My winform application doesn't work on others' pc without vs 2010 installed

    - by wings
    Just like I said, my winform application works properly on computers with VS installed, but on other computers, it will crash due to a FileNotFound Exception. I used using Application = Microsoft.Office.Interop.Excel.Application; in my source code to generate a Excel file, and the problem occurs as soon as the Excel-related function is called. But I don't know what it refers to exactly. Do I have to get some .dll included along with the .exe file? And what DLL is that? Below are part of my codes: private void FileExport(object objTable) { StartWaiting(); string[,] table = null; try { table = (string[,])objTable; } catch (Exception ex) { ShowStatus(ex.Message, StatusType.Warning); } if (table == null) { return; } Application excelApp = new Application { DisplayAlerts = false }; Workbooks workbooks = excelApp.Workbooks; Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); Worksheet worksheet = (Worksheet)workbook.Worksheets[1]; worksheet.Name = "TABLE"; for (int i = 0; i < table.GetLength(0); i++) { for (int j = 0; j < table.GetLength(1); j++) { worksheet.Cells[i + 1, j + 1] = table[i, j]; } } Range range = excelApp.Range["A1", "H1"]; range.Merge(); range.Font.Bold = true; range.Font.Size = 15; range.RowHeight = 50; range.EntireRow.AutoFit(); range = excelApp.Range["A2", "H8"]; range.Font.Size = 11; range = excelApp.Range["A1", "H8"]; range.NumberFormatLocal = "@"; range.RowHeight = 300; range.ColumnWidth = 50; range.HorizontalAlignment = XlHAlign.xlHAlignCenter; range.VerticalAlignment = XlVAlign.xlVAlignCenter; range.EntireRow.AutoFit(); range.EntireColumn.AutoFit(); worksheet.UsedRange.Borders.LineStyle = 1; Invoke(new MainThreadInvokerDelegate(SaveAs), new object[] { worksheet, workbook, excelApp } ); EndWaiting(); }

    Read the article

  • Reading excel files with xlrd

    - by snurre
    I'm having problems reading .xls files written by a Perl script which I have no control over. The files contain some formatting and line breaks within cells. filename = '/home/shared/testfile.xls' book = xlrd.open_workbook(filename) sheet = book.sheet_by_index(0) for rowIndex in xrange(1, sheet.nrows): row = sheet.row(rowIndex) This is throwing the following error: _locate_stream(Workbook): seen 0 5 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 20 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 172480= 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 172500 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 3 2 172520 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 173840= 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 173860 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 1 1 173880 1 1 1 1 1 1 1 1 Traceback (most recent call last): File "/home/shared/xlrdtest.py", line 5, in <module> book = xlrd.open_workbook(filename) File "/usr/local/lib/python2.7/site-packages/xlrd/__init__.py", line 443, in open_workbook ragged_rows=ragged_rows, File "/usr/local/lib/python2.7/site-packages/xlrd/book.py", line 84, in open_workbook_xls ragged_rows=ragged_rows, File "/usr/local/lib/python2.7/site-packages/xlrd/book.py", line 616, in biff2_8_load self.mem, self.base, self.stream_len = cd.locate_named_stream(qname) File "/usr/local/lib/python2.7/site-packages/xlrd/compdoc.py", line 393, in locate_named_stream d.tot_size, qname, d.DID+6) File "/usr/local/lib/python2.7/site-packages/xlrd/compdoc.py", line 421, in _locate_stream raise CompDocError("%s corruption: seen[%d] == %d" % (qname, s, self.seen[s])) xlrd.compdoc.CompDocError: Workbook corruption: seen[2] == 4 I'm not able to find any info about CompDocError or Workbook corruption, even less the seen[2] == 4 part.

    Read the article

  • How to effectively use WorkbookBeforeClose event correctly?

    - by Ahmad
    On a daily basis, a person needs to check that specific workbooks have been correctly updated with Bloomberg and Reuters market data ie. all data has pulled through and that the 'numbers look correct'. In the past, people were not checking the 'numbers' which led to inaccurate uploads to other systems etc. The idea is that 'something' needs to be developed to prevent the use from closing/saving the workbook unless he/she has checked that the updates are correct/accurate. The numbers look correct action is purely an intuitive exercise, thus will not be coded in any way. The simple solution was to prompt users prior to closing the specific workbook to verify that the data has been checked. Using VSTO SE for Excel 2007, an Add-in was created which hooks into the WorkbookBeforeClose event which is initialised in the add-in ThisAddIn_Startup private void wb_BeforeClose(Xl.Workbook wb, ref bool cancel) { //.... snip ... if (list.Contains(wb.Name)) { DailogResult result = MessageBox.Show("some message", "sometitle", MessageBoxButtons.YesNo); if (result != DialogResult.Yes) { cancel = true; // i think this prevents the whole application from closing } } } I have found the following ThisApplication.WorkbookBeforeSave vs ThisWorkbook.Application.WorkbookBeforeSave which recommends that one should use the ThisApplication.WorkbookBeforeClose event which I think is what I am doing since will span all files opened. The issue I have with the approach is that assuming that I have several files open, some of which are in my list, the event prevents Excel from closing all files sequentially. It now requires each file to be closed individually. Am I using the event correctly and is this effective & efficient use of the event? Should I use the Application level event or document level event? Is there a way to prevent the above behaviour? Any other suggestions are welcomed VS 2005 with VSTO SE

    Read the article

  • Change in behaviour & generation of nullreference exception

    - by peril brain
    I made this program 2hr ago and it ran quit well when i confronted this to presaved .xls file. But when i closed that and started new instance,it started generating null refrence exception why??plz explain. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.IO; using System.Threading; using Microsoft.Office.Interop; using Excel = Microsoft.Office.Interop.Excel; namespace svchost { class MainClass { Excel.Application oExcelApp; static void Main(string[] args) { MainClass mc = new MainClass(); while (true) { if (mc.chec()) { Console.WriteLine("RUNNING"); Thread.Sleep(4000); } else { Console.WriteLine("NOT RUNNING"); Thread.Sleep(8000); } } } public bool chec() { try { oExcelApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application"); Excel.Workbook xlwkbook = (Excel.Workbook)oExcelApp.ActiveWorkbook; //****PROBLEM FROM HERE********* Console.WriteLine(xlwkbook.Name + "\n"); ke kw = new ke(ref oExcelApp,ref xlwkbook); Console.WriteLine(xlwkbook.Author); xlwkbook = null; } catch (Exception ec) { oExcelApp = null; System.GC.Collect(); Console.WriteLine(ec); return false; } oExcelApp = null; System.GC.Collect(); return true; } } class ke { public ke(ref Excel.Application a1, ref Excel.Workbook b1) { Excel.Worksheet ws = (Excel.Worksheet)a1.ActiveSheet; Console.WriteLine(a1.ActiveWorkbook.Name + "\n" + ws.Name); Excel.Range rn; rn = ws.Cells.Find("657/07", Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing); Console.WriteLine(rn.Text); } } }

    Read the article

  • Runtime Error 1004 using Select with several workbooks

    - by Johaen
    I have an Excel workbook which pulls out data from two other workbooks. Since the data changes hourly there is the possibility that this macro is used more than one time a day for the same data. So I just want to select all previous data to this date period and want to delete them. Later on the data will be copied in anyway. But as soon as I want to use WBSH.Range(Cells(j, "A"), Cells(lastRow - 1, "M")).Select the code stopes with Error 1004 Application-defined or object-defined error. Followed just a snippet of the code with the relevant part. What is wrong here? 'Set source workbook Dim currentWb As Workbook Set currentWb = ThisWorkbook Set WBSH = currentWb.Sheets("Tracking") 'Query which data from the tracking files shoud get pulled out to the file CheckDate = Application.InputBox(("From which date you want to get data?" & vbCrLf & "Format: yyyy/mm/dd "), "Tracking data", Format(Date - 1, "yyyy/mm/dd")) 'states the last entry which is done ; know where to start ; currentWb File With currentWb.Sheets("Tracking") lastRow = .Range("D" & .Rows.Count).End(xlUp).Row lastRow = lastRow + 1 End With 'just last 250 entries get checked since not so many entries are made in one week j = lastRow - 250 'Check if there is already data to the look up date in the analyses sheet and if so deletes these records Do j = j + 1 'Exit Sub if there is no data to compare to prevent overflow If WBSH.Cells(j + 1, "C").Value = "" Then Exit Do End If Loop While WBSH.Cells(j, "C").Value < CheckDate If j <> lastRow - 1 Then 'WBSH.Range(Cells(j, "A"), Cells(lastRow - 1, "M")).Select 'Selection.ClearContents End If Thank you!

    Read the article

  • Excel Smart Find and Replace only specific characters

    - by Asim
    I want to change INT to INTERNATIONAL and NA to NATIONAL ASSEMBLY in whole excel workbook through an excel Macro or Find and Replace dialogue box. But when I run the macro or change it through Find and Replace dialogue box it also replace NA from CHINA last 2 characters and it became CHINATIONAL ASSEMBLY and INTERIOR to INTERNATIONALERIOR. Now, I want that Excel should only smartly find the character NA in the workbook which is not included with any other character likewise character INT which is not attach to any other character. I would be grateful if anyone give any formula, Excel Macro or anything else to overcome this issue. Thanks,

    Read the article

  • Excel (.xls) files not opening in Excel when launched from Explorer

    - by Alex Berry
    I appear to have done something to Excel (2003) whereby whenever I double-click on an Excel file (.xls) in Windows Explorer, Excel launches OK, but the corresponding workbook doesn't open, and I'm left with an instance of Excel with no active workbook, i.e. a grey screen. It was fine earlier on, but now it's goosed. It's clearly not windows file associations, as Excel launches ok. It's a problem specific to Excel itself... Any thoughts what I might have done to give rise to this? Many thanks, Alex.

    Read the article

  • Microsoft Excel Error

    - by Chris
    I am working with a user that has Office 2007 installed. When they open excel with a blank workbook and then they try to close excel without having done anything, it gets hung for about a minute or two and then gives the warning message: "Microsoft Office Excel is waiting on another application to complete an OLE action." Has anyone ever seen this happen before? If so can you please help me figure out how to resolve this. It doesn't matter what excel file they open or if it is a blank/new workbook. Please Help.

    Read the article

  • In Open Calc, how do I copy a paste cells that contain references to other workbooks?

    - by Gabriel
    I have an OpenOffice Calc sheet with cells containing formulas that refer to another file, e.g: 'file:///C:/path/to/other.xls'#$Sheet1.A1:B2. Note: When I copy the cells from one sheet to another in the same workbookt, it works as expected. When I copy this to a sheet in another workbook, the references are lost and all is left is ''#$Sheet1.A1:B2. I could copy/paste the cell's contents, but that would be fastidious, error-prone, and anyhow not a good solution. How do I keep the external references when copy-pasting from one workbook to another ?

    Read the article

  • How to change color of a cell in one sheet if its value increases or decreases from another cell value in a different sheet

    - by Sean Coffey
    I am putting together a workbook with 52 sheets for the weeks in the year. In this workbook there are multiple columns for percentages on each sheet. I want to be able to change the color of each cell based on whethernit increases (green) or decreases (red) from the same cell in the previous sheet. I have the formula i.e. =IF(ISERROR(B4/D4),"0", B4/D4) to calculate my percentages but I don't know how to put the reference in for this change. Any help?

    Read the article

  • How can I fix a date that changes by 4 years and 1 day when pasted between Excel workbooks

    - by lcbrevard
    In Excel dates are represented internally by a floating point number where the integer part is the number of days since "some date" and the fractional part is how far into that day (hence the time). You can see this if you change the format of a date - like 4/10/2009 to a number 39905. But when pasting a date between two different workbooks the date shifts by 4 years and one day!!! In other words "some date" is different between the two workbooks. In one workbook the number 0.0 represents 1/0/1900 and in the other 0.0 represents 1/1/1904. Where is this set and is it controllable? Or does this represent a corrupted file? These workbooks where originally from Excel 2000 but have been worked on now in Excel 2007 and Excel 2003. I can demonstrate the problem between the two workbook files in both 2003 and 2010. The exact history of when they were created or what versions of Excel have been used on each is unknown.

    Read the article

  • Excel 2010: dynamic update of drop down list based upon datasource validation worksheet changes

    - by hornetbzz
    I have one worksheet for setting up the data sources of multiple data validation lists. in other words, I'm using this worksheet to provide drop down lists to multiple other worksheets. I need to dynamically update all worksheets upon any of a single or several changes on the data source worksheet. I may understand this should come with event macro over the entire workbook. My question is how to achieve this keeping the "OFFSET" formula across the whole workbook ? Thx To support my question, I put the piece of code that I'm trying to get it working : Provided the following informations : I'm using such a formula for a pseudo dynamic update of the drop down lists, for example : =OFFSET(MyDataSourceSheet!$O$2;0;0;COUNTA(MyDataSourceSheet!O:O)-1) I looked into the pearson book event chapter but I'm too noob for this. I understand this macro and implemented it successfully as a test with the drop down list on the same worksheet as the data source. My point is that I don't know how to deploy this over a complete workbook. Macro related to the datasource worksheet : Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) ' Macro to update all worksheets with drop down list referenced upon ' this data source worksheet, base on ref names Dim cell As Range Dim isect As Range Dim vOldValue As Variant, vNewValue As Variant Dim dvLists(1 To 6) As String 'data validation area Dim OneValidationListName As Variant dvLists(1) = "mylist1" dvLists(2) = "mylist2" dvLists(3) = "mylist3" dvLists(4) = "mylist4" dvLists(5) = "mylist5" dvLists(6) = "mylist6" On Error GoTo errorHandler For Each OneValidationListName In dvLists 'Set isect = Application.Intersect(Target, ThisWorkbook.Names("STEP").RefersToRange) Set isect = Application.Intersect(Target, ThisWorkbook.Names(OneValidationListName).RefersToRange) ' If a change occured in the source data sheet If Not isect Is Nothing Then ' Prevent infinite loops Application.EnableEvents = False ' Get previous value of this cell With Target vNewValue = .Value Application.Undo vOldValue = .Value .Value = vNewValue End With ' LOCAL dropdown lists : For every cell with validation For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation) With cell ' If it has list validation AND the validation formula matches AND the value is the old value If .Validation.Type = 3 And .Validation.Formula1 = "=" & OneValidationListName And .Value = vOldValue Then ' Debug ' MsgBox "Address: " & Target.Address ' Change the cell value cell.Value = vNewValue End If End With Next cell ' Call to other worksheets update macros Call Sheets(5).UpdateDropDownList(vOldValue, vNewValue) ' GoTo NowGetOut Application.EnableEvents = True End If Next OneValidationListName NowGetOut: Application.EnableEvents = True Exit Sub errorHandler: MsgBox "Err " & Err.Number & " : " & Err.Description Resume NowGetOut End Sub Macro UpdateDropDownList related to the destination worksheet : Sub UpdateDropDownList(Optional vOldValue As Variant, Optional vNewValue As Variant) ' Debug MsgBox "Received info for update : " & vNewValue ' For every cell with validation For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation) With cell ' If it has list validation AND the validation formula matches AND the value is the old value ' If .Validation.Type = 3 And .Value = vOldValue Then If .Validation.Type = 3 And .Value = vOldValue Then ' Change the cell value cell.Value = vNewValue End If End With Next cell End Sub

    Read the article

  • Is there a way to get a shared spreadsheet to update without closing and reopening?

    - by Mike
    Using Excel 2010, I have a spreadsheet that is used by 3 different people at any one time. But if one person has the spreadsheet open on there PC the other people can only view it as read only. I have since shared the workbook and put the spreadsheet on a shared network drive and now they can all view the spreadsheet at the same time and edit it at the same time. The problem is that nobody can see the changes that the other users have made unless the close out of the spreadsheet and open it up again to view the changes. I have checked the settings of the shared workbook and on the advanced tab have tick the option that updates the information every 5 minutes but the information does not update until you close out and open the spreadsheet back up again. How can I fix this problem?

    Read the article

  • Virtualise Excel in a browser

    - by Macros
    Is it possible to give users access to a virtualised instance of Excel - I don't want to give them access to a full OS (although this will clearly be running in the background, all they can access is Excel - they don't even see any other screens)? Secondly, if it is possible, is it possible to do within a browser? Edit I am building a system which is designed to test candidates skills in Excel and for this reason needs to use the full desktop version and not a web app. I don't want to have to ensure Excel is installed on the client machine as there will be issues around differing versions and security as the workbook(s) that are used in the test use VBA extensively to customise and mark the exercises. Ideally my web app would be able to open a session to the server which then just puts the user into an instance of Excel without ever seeing a desktop. I would also need to be able to pass in command line parameters in order to define which workbook to open and also pass in a unique token to identify the user

    Read the article

< Previous Page | 2 3 4 5 6 7 8 9 10 11 12 13  | Next Page >