I have the following code as workbook module but it is not working, I have tried fiddling with it but I cannot work out what is wrong, can anyone help please.
Private Sub Workbook_Open()
Dim ws As Worksheet ....
I have a spreadsheet with some macros in it. When I run the macros, I would like to unprotect the worksheet and then protect the spreadsheets when the macros are done. The excel tabs I would like to protect/unprotect are called Graph and Data.
View 3 Replies View RelatedI created several macros objects to do simple hide columns, filter on, filter off, etc. type functions, using the Tools / Record Macro feature. Then discovered they won't work in a protected worksheet. Is there a way around this situation, other than turning protection off? Here's an example of the code for one of them, although I get the error or every attempt to perform a macro once the sheet is protected:
View 4 Replies View RelatedI now have the spreadsheet I want to distribute saved as an add in stored in a network repository. RoyUK has provided me with some links with code I lifted to add custom menu items.
The last problem I have is that I'm not trying to call a macro, but am instead trying to load the sheets (there are 4 sheets) and forms (there are 7 forms).
With what I have now, I can launch a macro with no problem, but cannot get the sheets / forms displayed to the user. I've tried launching a form by loading / showing it, but I get error message (runtime error 9).
I cannot run a macro because I am encountering error 1004, intersect of object. i am using a protected workbook, and I dont have the password to unprotect it, but it needs to be fill out using a macro from a different workbook. Is there a way around this?
View 1 Replies View RelatedI have a macro in a workbook (good start) as follows:
Sub UnhideSheets()
' Set to Auto Calc mode
With Application
.Calculation = xlAutomatic
End With
' Asks for a password (must be "unhider")
Dim mypass, response As String
mypass = Application.InputBox("This macro requires you to enter a password to proceed." & vbCrLf & "Please enter password below.")
' If password is correct
If LCase(mypass) = "unhider" Then
' Unhides all sheets
Dim i As Integer
For i = 1 To Sheets.Count
Sheets(i).Visible = True
Next i
' Then tells you that all is OK
response = MsgBox("All sheets unhidden", 0, "Macro run")
' Otherwise
Else
' Tells you that there is a problem (Unauthorised to continue)
response = MsgBox("Sorry, you are not authorised to use this macro.", 16, "Macro stopped")
' hides the sheet again
Sheets("BSG Only!").Visible = xlHidden
Sheets("CC07").Activate
' And ends the "if"
End If
End Sub
This unhides the "very hidden" sheets that make the book work and is password protected because I want to limit the people who can see this data.
What I would like to do, however, is call this macro remotely from another procedure in a separate book, which can only be accessed by those who are authorised to see the hidden data.
Essentially, then, my question is: "How do I call the protected procedure without the user having to enter the password each time, as they must be authorised to have access to the procedure that calls the protected procedure?"
I am getting an error when I protect a worksheet and save it. Upon re-opening the worksheet, I try to use the cells that are being formatted by a macro, but I get the following error...
Run-time error '1004':
Unable to set the NumberFormat property of the Range Class.
If I open the worksheet and it is already protected from when I last closed it, then I get the error. If I unprotect the sheet, then close it and reopen it, then I don't get the error. Does anyone know what I might be missing or doing wrong.
How do i get use macros when the excel doc. is password protected?
View 9 Replies View RelatedI have a macro with "ActiveWorkbook.Protect Password:="password"" . It was working fine in excel 2003. In excel 2007, because of this I am not able to run any macro's in the workbook. I am getting a message "The macro may not be available in this workbook or all macros may be disabled. I can see a security warning in message bar saying macros have been disabled, but I dont have have a option to enable the macros.
View 9 Replies View RelatedI have received following macro from someone to delete series but the problem is that it gives error when some sheets are protected, and I want those sheets to protected. When run it will ignore/leave protected sheets but delete series only from unprotected sheets
Private Sub CommandButton1_Click()
Dim Rng As Range, i As Long, r As Range, lVal, uVal
Dim DeleteCount As Double
Dim lRow As Long
Dim dr As Long
Dim dc As Long
dc = Sheets("Deleted Numbers").UsedRange.Columns.Count - 1
dr = Cells(Rows.Count, Sheets("Deleted Numbers").UsedRange.Columns.Count - 1).End(xlUp).Row + 1
If dr = 60001 Then ................
I am trying to password protect some of the cells in a worksheet. However doing that starts giving errors with Macros. The Macros are no longer working and infact giving error. What can I do to stop that happening?
View 9 Replies View RelatedI have a list of hyperlinks in excel with check boxes next to each, does anyone know how I can do a macro that will run another macro if the check box is checked and ignore the macro if it isnt checked???
View 3 Replies View RelatedWhen I send a workbook with macros to some of my users, they either can not open the workbook due to there being macros or they have to enable them each time the workbook is opened. I know there is a way to correct this, but I don't know how to look this up in help so I am coming to my trusted friends on Excel Forum. I would also like to know what this is called as I slightly remember that there is a name for this.
View 5 Replies View RelatedI currently use an Excel program (with macros) that run weather forecast data. It is compatible with versions XP, 2003, 2007 and 2010. However, I am trying to figure out if I can run it on a Mac. My plan is to run windows on my Mac through Parellels or VMWare Fusion.
Here is a link to the Excel Program: TX Tornado Forecast Worksheet
I have a macro created that at the mo is assigned to a button. But I would like the macro to run as soon as the spreadsheet is opened.
View 9 Replies View RelatedI usually run it on a different server while I work on another available server. However, if I am in Excel on a different server working, I cannot copy and paste in Excel (if I try to copy & paste, I end up pasting data from the macro). This makes sense since the macro is copying & pasting but is there any way around this? I purposely didn't past the code since it's huge but it has a lot of "cells.copy, activesheet.paste, etc".
View 9 Replies View RelatedI have a simple Macro. I assigned to a Form button.
when i click that button systems takes lots of time to complete the macro.
left down task bar it say calcuating cells and load from 1% to 100% ...
Excel 2003
I have three macros:
Sub Concat()
ActiveCell.FormulaR1C1 = "=IF(RC[-1] > 1,CONCATENATE(""*"",RC[-1],""*""),"""")"
End Sub
Sub InsertLocation()
ActiveCell.FormulaR1C1 = "=IF(RC[-2] > 1, R[2]C[2],"""")"
I need To have them excecute automatically once data Is enter into a cell In column A:
I 've attempted onEvent configurations starting with -
Sub FillScanSheet()
ActiveCell.EntireColumn.Select
If ActiveCell > 1 Then
Application.Run Concat
Application.Run InsertLocation
Application.Run Dupes
End If
However I keep getting a circular reference error --
I want to run a recorded macro on the whole workbook at once, not just on the active worksheet.
View 3 Replies View RelatedI have to run several different types of macros depending on the folders. each folder contains several excel files. so what i want to do is if a folder has a certain name run a certain macro, Here is the code:
VB:
Sub Macro1() '//Change the path to the main folder, accordingly
Call RecursiveFolders("C:Path")
End Sub
[Code]....
then call macro 2 end what syntax code should go there?
Our department receives weekly and fortnighly updated workbooks from a number of different departments. Each workbook contains 10s of worksheets. Our department analyzes the data and generates reports from those worksheets.
I have developed a few macros to automate the report generation for making the computing of my colleagues easier.
Now, the problem is that all the macros are developed behind individual sheets and some modules. For this reason, my colleagues have to copy each updated sheet and paste over the older ones individually, each and every time. It is really quite time-consuming task - deleting older data from all the worksheets and then copying fresh data on each of them, one by one.
They want to make their life easier by just replacing the entire workbooks with the new ones and still want the macros functioning, as usual.
My concern is how I can put all the code in a separate workbook and how to reference data from the other workbook.
I have been writing VBA code in Excel for about 4 years, but I am self-taught, so I only know what I have had to learn. Recently, I learned that I could run a macro from a cell formula. I created several hundred cells (on multiple worksheets) that include calls to various macros. I only want to run these when I know they need to be updated, not every time I change a cell value.
I turn on Design Mode and try to modify my worksheet (adding or removing columns, rearranging stuff) and some macro(s), somewhere(?) execute every time. This can sometimes take 15-20 seconds for everything to update.
Did I hit some magic key sequence, somewhere? How is it that some of my macros, somewhere are running with Design Mode enabled (and turning it OFF, by the way)?
Whenever I share a workbook, I experience problems running macros that are problem-free otherwise. I’ve read the help menu about restrictions of using shared workbooks, but I did not run across anything that might explain this behavior. I’ve experimented with a few spreadsheets and I run across this problem in all cases. Can anyone help with an explanation or past experience? I’m assuming that by sharing a workbook, the workbook is automatically protected for both windows and structure. If this is what is causing the problem, is there code I can use to still allow my macros to run trouble free in a shared workbook?
View 9 Replies View RelatedI have made a vba program in excel 2003 that opens a worksheet using Workbooks.Open, and copies all the worksheets out into the program etc. The worksheets that I am opening have macros that automatically execute when the worksheet is opened (opening up some forms ). These macros are running when I open the file using VBA. How can I prevent this from happening. The automatically executed code in the workbook being opened is located in "this workbook" and can be seen below.
Private Sub Workbook_Open()
Dim CfileName As String
'Check Config sheet for template state
If Config.Cells(10, 3) = 1 Then
CustomizeDirInfo.Show
End If
If Config.Cells(10, 3) = 2 Then
GetConfData
GetGrpData
StdFileGen.Show
Else
Exit Sub
End If
End Sub
Here is a sample of code I am having problems with:
Sub CheckBox1_Click()
Range("Make") = Range("Make1").Value
Range("Model") = Range("Model1").Value
Application.Run ("EnterPrice")
Aplication.Run ("Unit Selection")
CheckBox1.Value = True
End Sub
I am having problems with the named ranges "Make" and "Model" which are on another sheet and with runninh the "EnterPrice" and "Unit Selection" Macros which are in Module1, I get the error message "Method 'Range' of object '_Worksheet' class failed"
I am copying web data into Excel and need a creative way to run a couple formatting macros on the data. I’m looking for the best way to initiate the macros. I cannot use command buttons because they’ll get deleting as a result of the line “DrawingObjects.Delete”.
I’d like to use a Useform with a couple buttons but am not sure how to have it automatically display when needed and hidden when not needed. I also prefer not using toolbars button unless they will only be displayed in that workbook and not any others.
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?
I have been working on some Macros. I am compiling a macro that runs other macros. I have two noticeable problems.
1 - My SaveAs macro runs fine by itself. But when called or ran by another macro, (which calls more than one macro) the SaveAs macro it stops.
2- Many of my macros, when ran by another macro (which runs more than one macro) cycles through twice.
My specific question is about no. 2 and the following are the individual macros and the macro that runs them.
I have 3 macros, 2 of them puts formulas in 2 separate cells and the other to turn off file save, file save as and the Save button. Everything was working fine until I password protected the project, now the macros quit loading. I can still run them manually and they work fine, they just don't run when the spreadsheet is loaded. (What I wanted to do was prevent users from seeing the formulas by saving the spreadsheet after the formula was put in the cell and also by looking at the macro). I've unpassword protected the project and took out the macro to prevent the Saves. What do I need to look at to get the macros to run when the spreadsheet is loaded?
View 4 Replies View RelatedI have 3 seperate macros (macro1, macro2, macro3) that have to be run in order. Is it possible to create a macro to run them in order and what would the code look like? I will have it assigned to a button on the worksheet.
View 9 Replies View Related