Hyperlinks :: This Operation Has Been Canceled Due To Restrictions In Effect On This Computer
while trying to view a hyperlink i just created in a cell within a worksheet, a message popped up saying:
mircosoft office excell:
X this operation has been canceled due to restrictions in effect on this computer. Please contact your system administrator.
View Complete Thread with Replies
Related Forum Messages:
2 Lists And Finding New And Canceled Data Through VBA
I have 2 sets lists which should be identical, but every day I get give one new file which I have to compare to the other one. I need to find new data and removed data in the new file.
It is a dynamic list, but I need to check every day that all the data that was there yesterday is also there today. If not then I need to be told which data is missing and which data is new (maybe thrrough a pop up box and a new sheet with a new and cnacles coloumn).
A Example of the data is below. On day1 everything matches. On day 2 Grapes is a new entry, so I need this flagged up.
On day 3, pears hsave disappeared, I need this flagged up.
What would be great is to have a messgae box pop up and say "2 new entiries and 3 deletions - Please refer top Changes tab". And then the new/deleted entries will be in a seperate sheet.
I have tried searching this on the site, but can't find anything (I can't beleive problem like this is not on the website) - If there is an article would you be able to point me to it.
Input Box Instructions/restrictions For Filtering Data
Is there a way that I add instructions/restrictions to the push buttons on my form (basically they perform filter functions).
For instance, currently the only way to search the client or employee name is to enter the entire name. Is there a way to allow partial name searches?
Can I have a message appear that says "'Joe Shmo' can not be found".
Also I would like the input box not allow blank searches. If the input box is selected but then closed with no data entered, it filters for blanks which removes all data.
I have attached a sample of my form for you to look at.
Explicit Hyperlinks, Relative Hyperlinks
I have a spreadsheet and within the first sheet there are lots of hyperlinks to other cells within sheet 1 (my template).
I have made a small macro which very basically makes a copy of sheet 1 (my template)
The hyperlinks in the template are explicit and as they are copied to sheet two, they still reference back to cells in sheet 1. I need the hyperlinks to be relative, so that when I make a copy of the template the hyperlinks are copied and make reference to cells in the new sheet. I cannot work out how this should be done
Algorithm For Addition (operation For Each ID)
Have an excel table with following data:
- number of bottles
- number of bottle crates (there are 20 bottles in one one bottle crate)
I need to write a macro which will do this operation for each ID:
(bottles/20)-crates = x
and if "x" is not 0 then write down the value of "x".
There are two points I would like to point out:
- One ID may contain 3 or more rows (see 20168880)
- The macro will work with hundreds IDs so the algorithm should be fast (but it is not necessary)
Data - Text To Columns Operation
I have an issue with the Data - Text to Columns operation.
I paste some data into a sheet that is comma delimeted. I have a macro that runs the Data - Text to Columns operation and then moves the data to another sheet.(The data I need to paste is coming from a non-Excel application.)
I then copy a second set of data to another sheet and when I paste it, it automatically does the Data - Text to Columns operation but I do not want it to. I have seen this several times before and when I am manually manipulating the data it isn't a problem. When I use macros to manipulate the data, I don't get the desired results because my macro includes this operation.
I have done a little testing and this issue doesn't seem to always present itself. How can I paste the data without Excel trying to out-think me and just leave the data in the comma delimeted format it is in when I paste? What causes this? Its seems like Excel is seeing similar data and "assuming" I want to perform the same operation I just performed.
I would like to produce a carousel effect. i will start with a basic example and then explain the complications.
Basically I have a range of cells of indeterminant length. The left column is numbered vertically - let's say 1,2,3,4,5,6. I would like to be able to generate a carousel effect so that if I right click on a number all the cells below move down and the number at the bottom fills the gap so that if I right click on 1 the cells would become 6,1,2,3,4,5 or if I right click on 3 the cells would become 1,2,6,3,4,5.
The complication is that there will be more than one of each number eg 1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,5,5,5,5,6,6,6,6. (there will allways be the same number of each) so I would want the whole block to move down and the ones at the bottom to come up and fill the gap.
The other slight complication is that the range of cells is 5 columns wide so I would want the whole block of cells to carousel.
Error :: 'operation Is Not Allowed When The Object Is Closed
I keep getting an error in this code while trying to write the results of a query to a sheet in the current workbook. The error i get is 'operation is not allowed when the object is closed'... since i didnt close the recordset before trying to write the results to the worksheet, im at a loss to explain why im erroring...
Public Sub fillWorksheetWithData(dataSheet As Worksheet, conStr As String, sqlStr As String)
LastRow = dataSheet.Range("A65536").End(xlUp).Row
If (LastRow < 2) Then
LastRow = 2
dataSheet.Range("A2:A" & LastRow).EntireRow.ClearContents
Cost Average Effect
Example number 1:
You invested 10.000 Dollar in the year 1985 into a tempelton fund and waited 20 years. Result can be seen at many forums and stock plattforms
Example number 2:
In 1985 you had no 10.000 Dollar, but only 100 Dollar a month. You invested this 100 Dollar each month for 20 years.
How big will the difference be between example 1 and 2 ?
If a "share" will cost 100 Dollar, you will get 1 share for 100 Dollar. Next month share is down to 50 Dollar, you will get 2 shares for your monthly 100 Dollar. Next month share is up at 200 Dollar, you will get only a half share for your 100 Dollar. Its called cost average effect.
Tool should allow "play arround", to simulate various scenarios, talking into account growth rate, time period, monthly investment, fluctuation of a theoretical share. Idealy we would love to " import" e.g. famous fund history (eg. Tempelton, Pioneer) etc. to see how investment would have developed when a steady monthly payment would have been made.
Do Operation On All Sheets Except The First Sheet In The Book Every Time I Update It
I have a workbook that I would like the same operation on all sheets except the first sheet in the book every time I update it (which is once a week).
I get data (currency format) for all of my employees on a weekly basis. Unfortunately this data is shown as a negative number and I need to invert these values. (So I use the formula below)... but I have to do this manually for about 35 sheets and I figure I could automate this somehow with a macro. Here's what I do usually and what I would like the macro to do automatically:
I want to insert a column after column G.
I would like to enter the following formula into H2:
=IF(G4="u", "u", IF(G4
Hide Workbook Opening/closing Operation On The Screen
I am running a script that populates about 70 or so workbooks based on information from the database. Naturally this script is quite slow but what seems to take the most time is opening and closing workbooks. Is there a way to "hide" the workbook opening/closing operation on the screen? I have tried using the
Application. ScreenUpdating = False
this sped the code up but you still see the workbooks opening on the screen.
Do Piviot Table Effect VB Code?
I created a couple of piviot tables from the same sheet I had VB code in. Since the creation of these P tables the code has stopped functioning.
In a attempt to re-activate the code i moved the piviots to a new spreadsheet. But the code is still not working....
Invert The Effect Of Freeze Panes
I would like to freeze the bottom part of my spreadsheet, such that the user can scroll through the upper part but still be able to see the bottom part. The freeze command freezes what is above the line, I want to freeze what is below the line.
Keydown To Effect To All Controls That Support Event
Is there a way to fix a private sub to catch keydown effect of various combos and textboxes of my userform?
Instead of typing the same thing to each and every control's keydown event just call a sub. Code goes like:
Select Case KeyCode
ShellExecute 0, "open", "hh.exe", ThisWorkbook.Path & "1.chm", "", 1
If MsgBox("Backup now", vbQuestion + vbYesNo) = vbYes Then BackUp
Col = 3
Instead of that, if i place code in a private sub test() just call test in keydown event
Charts: Vary Colors By Point With Area Fill Effect
I am attempting to create simple charts to show survey results 2005 v 2006. I have a single series in a bar chart - chart type. The wizard initially makes both bars the same fill color. I right-clicked one of the bars to start the Format Data Series Wizard. On the 5th tab, "Options", I ticked "Vary Colors By Point". A new color was assigned to the top or first bar. So far so good. But instead of these colors, I would like to use a different fill effect for each data point in the series
So I walk through the wizard and desiqn a fill effect and click ok. The fill effect is assigned to both data points. I checked where I had ticked "Vary Colors By Point"
It is still ticked. Can I override this somehow so that each data point can have a unique fill effect?
Get Computer ID
Is there a code available that can be used to get the user's computer ID number (Not Computer Name)? I'm using a password generator to randomly generate a password and I would like to use this unique password with the user's Computer ID.
Computer IP Address
I have a problem running an excel macro on different laptops because the IP needs to be known (my program links to databases that are installed on a "server" laptop). The IP can be sourced by using the "Start, Run, cmd, ipconfig" but my users can have problems with process.
Does anyone know of a vb function that will display the ip of the computer that the code is run on?
How To Restrict A W/b To A Specific Computer
I've a workbook (XL 2003) test-1.xls, and I need to restrict it to a specific computer with ID: my-a1234567k
(shown under ControlPanel::System::Computer Name::Full computer name).
Is it possible to code such restriction in the w/b Open() event to prevent opening the w/b on a different computer?
My computers each has a different OS (Win 2000, Win XP Home, Win XP Prof ), different Excel version (XL 2000, 2003, 2007), etc., and the majority of my workbooks have been developed and would work only in certain environment (e.g.; in XL 2003 only, or XL 2007 only, ... ).
Module Runs On One Computer But Not On Another
I wrote some code that runs with no problem on my computer as well as another coworkers computer, but will not work on a 2nd coworker's computer. The "subscript out of range" error shows up at the point when the EMInput worksheet in the EMInput workbook is copied to the new workbook called "Out of Plane Section Cuts" (SEE BELOW). Is there an option in EXCEL or somewhere that could affect this. I don't see why you would get a subscript out of range error since the EMInput workbook is open....
How To Get To A Computer On Our Network Using Code
I have code below that works well most of the time. One of the cases below will not work. It insists on requiring a name and password.
(note: it is the only Windows 2000 operating systems)
I want to enter this in my case line to automatically add the username and password.
Let's say the username is: "dave" and the password is: "happy"
How can I add this to this part of the code?
strfilename = "\DAVEJONESDavesProposals" & strfilename
Dim strPath As String, strPath2 As String, CurrPath As String
Dim WB1 As Workbook
Dim WB2 As Workbook
Identify A Specific Computer
I have few macros that I use to reconfigure a particular workbook into a sort of "developer mode". I keep them in a text file that I can paste them from when I'm working on it and then delete them before I send it out to other people.
I'd like to be able to script them so they only run if the workbook is open on a specific machine (namely mine). Is there any way to identify the specific computer a spreadsheet is being run on?
Multiple Versions - One Computer
I'm running Excel 2007 with Windows Vista. I also have Excel 97, which I was told would not run with Vista. I've since discovered that many programs that I was told wouldn't run with Vista seem to work fine.
Is it possible to install Office 97 on the same machine with Office 2007?
Hyperlinking Info From Another Computer
In my restaurant we have a master computer upstairs that collects all of the register sales info. Then we have a computer downstairs that my employees sign in and out on.
This excel timesheet keeps track of the amount of labor hours being used per day. Is there a way of doing this without putting the timesheet on a web page or putting my sales info on a web page?
Computer Game Database
I want to make a computer game database with excel, but do not know how to start..
i want to be able to enter the name, publisher, year, system and genre i.e.
Name ? A Game
Publisher ? Pub
Year ? i would like a drop down menu to choose the year
System ? as above
Genre as above
Also i would like to be able to add a screenshot of the game if that is possible
Changing Computer Date
I have a form to imput a date but the problem is that it changes the computer date aswell. Is there another code i can use? Im using CDate code.
Private Sub CommandButtonProceed_Click()
Sheet2.Cells(2, 57) = TextBoxInitials
Cells(1, 2) = TextBoxSiteName
Cells(2, 2) = CDate(TextBoxDate)
Date = Cells(2, 2)
Sheet2.Cells(3, 28) = TextBoxPlant1
Sheet2.Cells(3, 31) = TextBoxPlant2
Sheet2.Cells(3, 34) = TextBoxPlant3
Sheet2.Cells(3, 37) = TextBoxPlant4
Sheet2.Cells(3, 40) = TextBoxPlant5
Sheet2.Cells(3, 43) = TextBoxPlant6
Sheet2.Cells(5, 27) = ExtractElement(Date, 3, "/")
Macro Does Not Run On Different Computer In Same Network
I wrote a macro that runs perfect when started from my pc, but if started from a different ps which is logged into the same network it doesn't run, also the macro is stored on the server. I have a code in the private section of the workbook:
Private Sub Workbook_Open()
then the macro 'FINAL' is stored in the 'Modules' section and starts with:
'this part opens the Edit/Links dialog from which the user has to choose the relevant model
Dim Which_financial_model_contains_the_data As String
ActiveWorkbook.ChangeLink Name:= _
"T:DEALSSSalsa (Project)Financial modelsProject Salsa Model - BASED ON SPONSOR CASE - b - 20061214.xls" _
, NewName:= _
Run-time Error '1004' :: Run On Other Computer
I have a piece of VB code that works perfectly on my pc. The spreadsheet has been emailed to a partner (different company) and the code no longer works and they get the run-time error.
Private Sub cmdPrintandsave_Click()
Dim Company As String
Dim irow As Long
MyDate = Date
Dim wsD As Worksheet
Dim wsD1 As Worksheet
Set wsD = Worksheets("AppBDataSheet")
Set wsD1 = Worksheets("AppBPrintable")
Company = Application.OrganizationName
Application.ScreenUpdating = False................
Save Settings Wizard For New Computer
I was reinstalling office on a laptop where the hard drive had been replaced. I used the 'save settings, wizard on my desktop and put in the new settings when I reinstalled Office.
This almost worked, but not quite.
Word and PowerPoint seem to be fine but when I reopened Excel I got a number of messages saying that objects could not be loaded because they were not on the machine. Some of this was because Excel was trying to reference add-ins that needed to be reinstalled. I have done that, and there are now fewer messages - but there are still two and I can't get rid of them. One looks for 'objects' the other a single 'object'. There seems no way of finding out what the objects are in question, and what it that Excel is stumbling over. Can I do anything other than abandoning my previous settings and starting again, or else living with having to click twice every time I start excel to get rid of the messages?
I have tried uninstalling Excel and reinstalling but this doesn't help. Perhaps registry changes are the problem...and I hate the registry.
Copy Protection :: Unique To A Computer
I understand that security in Excel is not very robust, but the users I intend to distribute a workbook to are not very sophisticated
Are there any system properties that are accessible to Excel/VBA and are unique to a computer that could be encoded in a hidden/protected cell, so the workbook would be frozen if copied to another computer?
Eg the user emails me that property which I enter in the check cell, and upon opening the workbook the check cell is compared to the system property, locking it up if the check fails?
Open My Computer In Windows Explorer
The following 3 macros do the following.
1st macro . . . When the button is clicked for this macro, Windows Explorer is opened and the folder MyMenuFiles is selected. Works Fine.
2nd macro . . . When this button is clicked, the windows explorer opens with My Documents selected. Works Fine.
3rd macro . . . THIS IS THE PROBLEM ONE. When this button is clicked, windows explorer opens, with the C: drive selected. I want My Computer selected.
Programme Doesn't Work When Sent To A Different Computer
I have a programme which works fine on my computer at home but when I send it via email to work the 'sort' function messes up (there is a chance it could be the 'vlookup' function, though) and all the cells in the sort range lose their formula reference - which is of course a tad annoying.
The part of the programme I'm having difficulty with is this:
Range("SsNo1").Resize(d, f).Offset(a, 0).Select
Selection.Sort Key1:=Range("FRSet1"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
Macros Cannot Be Found, Running On Another Computer
I have sent an Excel application to a colleague's computer. When she tries to access/run any of the macros (assigned to buttons), Excel displays and error inidcating that they are not found.
Looking under Tools/Macros they are there.
On my computer the a macro name is for example "Submit", preceded by the full path and file name. On the colleagues computer they show up as "Module5.Submit" (with the current path and filename), where the module number is the actual module number displayed in the VB Project.
Is there a VB setting that is responsible for including the module number and how would I turn this off?