Macro Functioning For One Workbook But Not Another
Feb 12, 2010
I have a macro that I've been using for a while now for one workbook that I want to use in another workbook for the same purpose. But, when I try to run it in the second workbook it gives me a Runtime error (32809). Here is the macro:
Sub invisible()
If Range("G16").Value = True Then
ActiveSheet.Shapes("OptionButton13").Visible = False
ActiveSheet.Shapes("OptionButton14").Visible = False
Range("A36").Value = "Blah Blah Blah"
Range("C36").Value = ""
Range("C37").Value = ""
ActiveSheet.Shapes("Check Box 182").Visible = False
Range("I11").Value = "Blah Blah"
Range("K11").Value = ""
Range("B36").Value = True
Else
ActiveSheet.Shapes("OptionButton13").Visible = True
ActiveSheet.Shapes("OptionButton14").Visible = True
Range("A36").Value = "Blah Blah Blah"
Range("C36").Value = "No"
Range("C37").Value = "Yes"
ActiveSheet.Shapes("Check Box 182").Visible = True
Range("I11").Value = "Blah Blah"
Range("K11").Value = "100"
Range("J11").Value = False
End If
End Sub
It is giving me the runtime error on this line:
ActiveSheet.Shapes("OptionButton13").Visible = True
What could be the problem?
I need to know how to make a workbook (and any/all copies made there from) cease to function (become irreversibly read-only) after a specific period of time following the first time it is loaded by a user (this would be preferable), or after a specified date (this could also work).
how to stop macros running on a particular worksheet within a workbook. I have a workbook monitoring vehicle faults with a control sheet. The control sheet displays the reg no and a "traffic light" indicator highlighting faults next to the reg no. Traffic light is turned red and green by If blank control. Each vehicle has a worksheet where faults can be recorded and a menu bar at the bottom of the screen operates numerous macros to "email worksheet"-"clear and copy to history" etc etc. The menu bar though can also be viewed and operated while in the control sheet. If a macro is activated within the control sheet it destroys it as it was not designed the same as the worksheets. Can I programme the menu to be inactive when in the control sheet OR that the menu will only appear when in every other sheet.
I am using excel 2013 in my laptop (windows 8.1) after having upgraded excel 2007. Everything works fine during my daily tasks. Especially autofill which I use quite often works perfect.
At the same time I upgraded my girlfriends laptop (Windows 7) with the office 2013 (office 2007 was the older version) and somewhere there begun some isues. Autofill doesn't. Especially with months or days. I enter the value "Monday", I drag down (of course using the black filled cross down right of the cell) the cursor and all the cells get the value Monday. Even if enter the value Tuesday in the second cell and then try to autofill (having marked before both these two cells) I get the same results. Monday, Tuesday, Monday, Tuesday.
I tried to test the autofill with numbers. If i write only the one number (for instance the value "1") and then autofill, this doesn't work. It gives me the value "1" in all the cells. But if I enter the value "2" in the second cell, the autofill works fine (marking these 2 cells and then autofill).
We make some online courses using exactly the same excel exercise files. In my laptop everything works perfect.
Code: Sub CopyRow()' 'Copies row to new sheet, highlights it, marks column 'A' as copied. ' Dim cCell As Range Set cCell = Selection.Cells(1, 1) Selection.Copy Sheets("Sheet2").Select Rows("2:2").Select
[Code] .....
Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.
I have data in 2 different workbooks, and I need to transfer all data in 'Jx_ex1' to sheet1 in the 'template' workbook, and all data in 'Gr_ex1' to sheet2 in 'template' workbook. All data should be transferred to the same cell numbers, from A1 to Y5000. I actually have loads of workbooks to transfer to the template but if I have an example with the two then im sure I can work out how to modify it. All files will be in the same folder.
I have a Macro that is stored in a specific workbook,"Projection Modeler.xls" the last line performed in this macro is the close workbooks without saving chages(this needs to be done due to the way this macro was written, and it would take too long to rewrite). If I would then like to reopen this workbook, and then rerun this macro, can I do this from inside the current macro (in a new sub routine, for instance), or do I have to write a macro outside of the workbook and then open the workbook and call the routine?
I have a workbook that has a lot of formulas in it that reference material stored on my local computer.
I need a macro that will copy all 42 tabs in to a new workbook book without all formulas, but saving all the formats have made. This would save me from every other day coping and pasting manually each of the pages.
I have this nice macro that copies data from another workbook. I want to make it to also add the filename of the workbook (for example data.xlsx) it copies the data from, in a cell on the actual workbook (for example on sheet "Combined", Cell A10). What should I add to my macro?
I am creating a spreadsheet to provide to a group of about 40 people. The spreadsheet allows each member to track the referrals they receive and give. I need to plan on creating updates for the spreadsheet and want to make it so they can easily transfer their data to the most current version in the future. Here is a breakdown of the workbook:
Worksheets: Members Lists Referrals Given Referrals Received Totals Formulas
I need to copy the data that they have input from the following worksheets: Referrals Given Referrals Received
Here is a breakdown of the "Referrals Received" worksheet: A1:V4 - Data that will not change and includes many named cells A5:Nxxx - Dynamic Named Range where they input their data O5:Vxxx - Contains formulas
I need to be able to copy A5:Vxxxxx from the old workbook to the new
Some side items to note: There is VBA in the worksheet of Private Sub Worksheet_Change(ByVal Target As Range) and included in the code is an Unprotect & Protect code to always keep the sheet protected
I figure whatever works for this sheet will work for all the sheets.
Is there a way to run a macro in an open workbook when the workbook becomes activated or deactivated? So if I have workbook A open with a macro in it and workbook B open as well, can I have the macro run on workbook A when that becomes the workbook I see on my screen?
I need to copy several macros from the Personal Macro workbook to a new workbook that I need to take to another computer. I need to be able to run the macros on that computer. I can only record macros and make minor changes in the VB editor. I do not know VB code.
I have a spreadsheet which has all the names of trips from a warehouse, the day that they operate (1,2,3 etc) and the job line allocated to each trip. It looks something like that:
A B C DAYTRIPCUST
I would like to create a macro that will be creating 7 new workbooks and then in those workbooks as many sheets as the trips. In these sheets, the customers should be displayed.
How do I write it? I could not find how to have a "dynamic choice" in the macro. I.e. not to have the criteria as "1", "trips1" but to choose from the range of inputs that are available.
I run most of my macros out of a file called "Personal.XLS" and I have this bit of code at the end of this particular macro which is closing personal.xls instead of the active file it is working on. It is set up on a loop and it should go to the next file in the directory but since it closes the file I run the macro from, the macro stops immediately. It appears that it believes "ThisWorkbook.Close" is referring to the file from which the macro is initiated.
I am using the following code to save a workbook as a macro enable workbook on the selected path as today's date. But it is being saved as a macro free workbook.
Below is the code in module:
Sub save_file() Dim tDate As String Dim FileSaveName As String Dim fPath As String tDate = VBA.Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd-mm-yyyy") 'FileSaveName = Application.GetSaveAsFilename(InitialFileName:=tDate, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
This is the code I use to call a macro when the macro Im calling is in the same workbook.
[Code].....
However, I would like to call this same macro when using another workbook. I copied the macro "Clearformating" and pasted into a personal macro workbook module. However when I add this code to the sheet tab it will not run the macro.
I have a question concerning Macros and the Disable/Enable prompt. My boss has a spreadsheet which has been used for years and he recently wanted to and did remove the macros from the spreadsheet (they were no longer necessary), but the disable/enable prompt still appears when the spreadsheet is opened. I replicated this in a test spreadsheet with a simple insert line macro and received the same results. Is there a way to remove the macros and the disable/enable prompt once they are removed? I know about setting the security to low to not see the prompt, but I would think that once the macros are deleted, the prompt should not appear any more.
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
I've created Keyboard Shortcuts to run Macros in "Workbook A". Problem is, if I have "Workbook B" open in the same Excel instance/process as "Workbook A" the Keyboard Shortcuts will run the Macros in "Workbook A".
Is there a way to restrict the Keyboard Shortcuts to only run the Macro if Workbook A is active?
Or is there a way to code the Macro to run its procedures only if Workbook A is active?
I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.
Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"
Writing a Marco. I would like to place a macro on the opening page of a workbook and like to a button that executes three functions.
1) I need to hide selected tabs in a workbook 2) I need to copy paste values of a couple select cells in a workbook 3) Lastly, I would like to set up the workbook so that the use can not move beyond the opening tab with out clicking the macro to preform the steps above.
Is this possible? I know 1 and 2 are for sure, what about 3?
I'm looking to use a macro that says that every time a change is made on column H in one sheet (Test Book1), that change is recorded (Record Name, Revision Date and Revision #) on another (Log Book1).
I realize I would begin with a worksheet change operator, but past that I'm stumped. I would then need to reference column C and H onto the Log Book1 worksheet.
I have a vba Project that asks the user if he/she wants to add an option. If he or she choses "no", the the sub exits. If they choose "yes" then the code opens a workbook template, saves it with a specific name, and takes the user there.
What I need is for a specific macro to be run, (exists in the template book) only when opening the workbook through this method.
