I need to use custom "Refresh all" function, so i set ctrl+alt+F5 to mine one and trying to hide/remove "Refresh all" from command bar control "Data" i tried different ways and no effect
VB:
Private Sub Workbook_Activate()
Application.CommandBars("Worksheet Menu Bar").Controls("Data").Visible = False
End Sub
[Code].....
I have a spreadsheet which has 3 separate external queries running from worksheet 'A'. My user enters a couple of dates in another worksheet 'B' to supply the date parameters for the query and a macro runs to refresh all of the queries and performs a few calculations.This works fine. The trouble is I don't want the user to be able to view all the data on sheet 'A' only the summary on B. My macro ends up on sheet B but whilst the query is refreshing the raw data is displayed to the user on sheet 'A'. When I hide the sheet 'A' i get a run time error '1004' Select method of worksheet class failed.
View 4 Replies View RelatedI am using Excel 2003. I have created a workbook containing two sheets. The first sheet is designed as a form for our managers to complete. The fields they are required to complete are based on the selection they choose from a drop down field (set up through data validation).
I have created a command button which when you click it opens up the second sheet of the workbook asking you to complete the individuals work pattern. I am trying to hide this button so that it only appears when you select certain options from the drop down field.
I have looked back through posts on here and have tried adding the VBA code to the worksheet around commandbutton1.vissible = False in an IF statement but can't get this to work. A colleague has suggested that you can't hide command buttons because they are fixed items - is this the case.
Below is the code that I copied from another thread. But what I need is to enable this command when the sheet is protected.
View 9 Replies View RelatedI would like to write code that opens the Excel Data menu, and then selects the Sort option, then stops. I do not want to do the actual sort (ie clicking the OK button), but I just want to emulate what would happen if the user actually clicked the Data menu, and then the Sort option. I would also like to disable the Options button and the "My list has header row, no header row" option buttons.
View 2 Replies View Relatedi have an excel spreadsheet with 27 or so workeets. it contains sales figures in it.
I want to be able to link mutiple cells of this workbook to another workbook so that it retreives that data, So that when I hit the refresh (!) button it will automatically put the data in. I will recieve new sales figures (new files) on a monthly basis so i want it to be able to update the figures to the new figures.
I have a custom command bar & and I have several buttons in it.
I only want some buttons availble when a certain worksheet is active.
What code would I use to only make certain button available.
ie if Budget worksheet was active then the botton below would be active?
But if Overages worksheet was active the button would be inactive?
Private Sub AddMenuToCommandBar(cb As CommandBar, blnBeginGroup As Boolean)
Dim m As CommandBarPopup, mi As CommandBarButton
If cb Is Nothing Then Exit Sub
Set m = cb.Controls.Add(msoControlPopup, , , , True)
With m
.BeginGroup = blnBeginGroup
.Caption = "Page Go"
.TooltipText = "Page Select"
End With
I would like to know how to configure buttons:
In tab Sheet1 there is Commandbuttons named "Sheet2" & "Sheet3". If i press "Sheet2" then i will be directed to sheet2 tab, same as "Sheet3". In tab Sheet2 & Sheet3 there is commandbutton "Sheet1", if i press the button then i will be directed to default tab which is sheet1.
how to change the color of a command button created by using the control tool box
View 2 Replies View RelatedI am trying to capture with Worksheet Change a command bar action like paste but I am getting an error :
“User-Defined type not defined”
Do I need a library of sort in References ??
Public Sub Right_Click()
Dim oControl As CommandBarControl
For Each oControl In CommandBars("Cell").Controls
Debug.Print oControl.Caption
If oControl.Caption = "&Paste" Then
oControl.OnAction = "MyPaste"
End If
Next oControl
End Sub
I have a query with ODBC connection to a SQL database. This query's parameter is linked to a cell. The resultant data is the source for a pivot table. I want to refresh the pivot table, when the query is run. I've tried using the cell that triggers the query....but the problem is that the query takes about 10 seconds to run. By the time the query returns new data....the pivot has already refreshed. I need it to refresh AFTER the query is complete.
I tried adding a cell that sums up the data from the query...thinking when THAT changes (due to updated data), to trigger the pivot refresh. Problem is that I don't know the trigger for when the sum cell changes (ie....formula change, not typed in.)
vba in excel 2013 pivot table that updates from an online CRM rows are a to h and it can be any number of rows. What I am trying to do is track progress. In column f values indicate probability for success 10 25 45 90, which can go up or down. The pivot table is refreshed to get the latest values from CRM. the update is handled by a connection to the crm not in the vba.
So far I been researching methods to conditionally format values that went up, down or remained the same since the last refresh with up down and across arrows. I have managed to piece together what I think should work but alas it is not. So I have come to you internet.
My code first clears any formatting and goes down the column avoiding null or empty cells, comparing the values in column f to values in column j.
I have 3 conditions greater than, less than or equal to, and would like add an icon for each based on the result of the comparison.
Finally when it finishes the column the code copies the current values in the pivot table column f to column j outside the pivot table which i hope to be able to hide once the cf works. The code is below
[Code] .....
I have a workbook with many worksheets and I want to enable (or disable) it to "Refresh every n minutes" for ALL worksheets, as at the moment it seems I can only specify this option per worksheet rather than the whole workbook?
View 1 Replies View RelatedI have an excel file with a link to an access database. when you open the excel file it prompts to refresh the data enable/disable. I select enable and the excel file is saved as a different file and the query reference in the new saved file is deleted (static version)
I have managed to write code to remove the database link from the new saved as file but what I still havent figured out is that the source excel file is refreshed automaticly when the script is running. I have set the database properties on automatic refresh on open which works if I activate manualy the file but with script down below, it does not refresh the data automaticly. What is the VB code to refresh the data in the source XLS file??
Sub main()
Dim prompt As Long
prompt = 200503
Dim objExcelApp As Object
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.displayalerts = False
objExcelApp.Workbooks.Open "c: emp est.xls" 'this file has the connection with the access database.
objExcelApp.Visible = True....................
This may be something very simple, but i have a table which collects varying data from different worksheets to finally calculate a Work in Progress result. Each line on the table consists of the same formula, and certain columns are used to changes %s which change the result.
When the data is changed, the formulas work through and update the new values with no problem until the last couple of columns. With these i am finding I have to copy and paste the formula back into the same place to refresh the data. The formula for those cells which causes the problem are:
Column X:
=IF(AE87="TRUE",(IF(G87>100,100,105)),(IF(AE87="Y",F87,(VLOOKUP(G87, lookup,2,FALSE)))))
Column Y;
=+W88*X88/100
Column Z;
=+U89-H89+Y89
Column AA and AB and AC:
No formulas
Column AD:
=AND(W87<0,X87>70)
Column AE:
=TEXT(AD87,1)
I have checked in the options and the calculation is set to automatic which I presume can cause these problems.
I have a macro that first refreshes a specific data range that imports data from a text file as such:
View 2 Replies View RelatedI currently have a spreadsheet that has data connections to a file on a shared drive. On opening the file I have it update the data connections so that the data needed is always correct. The file I use is to be distributed out to work colleagues to use and on testing it works really well apart from if you have the file open and somebody tried to open the original file where the data is pulled from for the connection. It says it currently in use by 'another user'.
Is there any code to add to workbook_open that will close/disable the data connection links once the data has been updated? The code I currently have is this:
VB:
Private Sub Workbook_Open()
ActiveWorkbook.RefreshAll
Application.Wait Now + TimeValue("00:00:02")
Userform1.Show
End If
End Sub
What this basically does is gives it time to refresh the data connections and show a progress userform. The only thing is the connections stay live and I don't want that as the connection file is then locked. I'm hoping its something simple like ActiveWorkbook.CloseDataConnection added in after the userform has been shown (or something along them lines ). Also the data connections would have to be able to reconnect on open so that they can refresh again.
I'm using Excel 2010 to link a table to a XML file on my server. I'd like to distribute the Excel file to a group of people and have it updated every time the XML file is updated on the server.
In Excel, I'm pulling the data using "Data", "From Web" and then I type in the path of the XML file.
Excel then builds a table with all the data but if the XML file is updated on the server, the data remains static, even if I click "Refresh" or "Refresh XML data" on the table.
It should be able to download new data including new columns to the table, if any.
I have a report that uses external data to feed a Pivot Table.
I noticed that the data source had been turned off automatically by Excel and turned it on and added the file location to the trust centre.
I also set the data to refresh when the workbook is opened.
I saved the file and then tried to refresh the data which caused Excel to crash. Of course now I can't open the file either because when it attempts to refresh itself on opening that causes Excel to crash as well.
Addendum: I have removed the Trusted Location which has caused Excel to disable the connection again. This has solved the crash on opening but I still can't refresh the data.
So I have a column, say column A, that I have a auto filter assigned. I would like it to auto-refresh the filter every time I add in data at the end.
I found this macro, but it only works for changes made within the filter, not if I add in data at the end. [URL]
Private Sub Worksheet_Calculate()
If Me.FilterMode = True Then
With Application
.EnableEvents = False
[Code].....
I have a userform that collects data from a worksheet, if I press back on the userform to take me back to the sheet and then change the data, when I fire up the userform it doesn't update with the new data?
I've put DoActions in UserForm_Initialize tried userform1.repaint and nothing works..
To get from sheet to userform there is a button that valdates the data before showing userform1 so it should always run the UserForm_Initialize at a guess?
I have a spreadsheet that has about 40 pivot tables that use label filters for filtering HH:MM and "greater than" selection. When I do a refresh, I have to go back and reset the label filters. Is there any way to not have to re-do every label filter after refresh? I have looked at all the settings and didn't see anything in there that looked like it would work. Maybe VBA?
View 4 Replies View RelatedI have an excel workbook that includes data imported from a TFS query. I have a VBA macro that refreshes the TFS data, but since it behaves as though the user is clicking the "refresh" button, it prompts the user, warning that this will overwrite unsaved data.
I need to run this on a loop so that it continually refreshes and writes a status message out to a file that gets used elsewhere, but it won't work if it prompts after each loop.
Is it possible to disable this alert, defaulting to "Yes", as in overwrite? I've tried Application.DisplayAlerts = False, but that doesn't seem to cover this alert.
I attempted doing this by recording the macro but the macro doesn't read me clicking onto Bloomberg Refresh Data Button. How can I do this using VBA?
View 1 Replies View RelatedHow can you make a ListBox RowSource show only visible data from a filtered range and then when the filtered range changes with new filter criteria, show the new visible data?
I Have a Defined Name Range named “Quantity” with the ....
I am having some issues using 'refresh all' to update my excel spreadsheet. Basically, it doens't seem to like doing anything greater than 65 queries before it gives me the error "Too many client tasks".
I've written a macro that updates all spreadsheets seperately, but this takes forever (I have approximately 250 worksheets within this workbook). Is there a way to speed this up? I believe the most time consuming part is establishing the connection to the access database. Is there a way to establish the connection to a database, and keep it open until all data has been updated?
Below is the code I am using:
Sub RefreshAll()
Dim e As Double
Dim sht As String
e = 1
Application.ScreenUpdating = False
Do
Sheets("All ESNs").Select
Cells(e, 1).Select
sht = ActiveCell.Value
Sheets(sht).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("All ESNs").Select
e = e + 1
Loop Until IsEmpty(ActiveCell)
Application.ScreenUpdating = True
End Sub
"All ESNs" refers to a worksheet that contains a list of all serial numbers. These serial numbers correspond to the worksheet names.
I searched, but couldnt find a thread which covers a VBA code which covers writing a conditional format into cells and changes each time contents of the cell are refreshed. The conditions are:
If activecell.value < 0 Then
.interior.colorindex = 3
If activecell.value >=0 Then
.interior.colorindex = 4
and i want this to happen for a big range of cells (range(a1:k1500) So basically, once the macro is run(which has the code to insert the conditinal formating for all the cells), if the user makes any change to the cell(within the range), the color of the cell should change automatically(like Conditinoal formating works by default). Prior to this, i was doing my cell coloring using the 'for loop' approach, but that of course does not reflect once the macro has ended.
I am trying to set up a button that can be used to refresh data from an external source. The problem is that I'm not sure what to put in the area that I am using the "query" variable
Sub refresh_data(ByVal WS As Worksheet, query As String)
Application.DisplayAlerts = False
Sheets(WS).QueryTable(query).Refresh
Application.DisplayAlerts = True
End Sub
what i end up getting is an error saying basically that I am using incorrect data.
Ok so I have an extensive worksheet that pulls a lot of data from the web, 47 connections to be exact. So on my work computer that runs excel 2007, the data refreshes instantly and I can see all the numbers flying through and changing as the sheets refresh.
However, my personal computer running excel 2010 hangs up with any type of data refresh, not just this sheet in particular. This computer has 8 GB of ram so I don't think that would be the issue?
Does excel 2010 have an issue with data refresh from the web?
Is it possible to run a macro (concatenate) automatically on a sheet when data is refreshed on that sheet from access database? It there a code that has to be written in vb?
View 5 Replies View Related