I have a website that I have been posting filtered worksheets of specific formats of items for interested viewers to view. The Looker can click on the Button and Open or Save a Copy of the Worksheet. I hide unused rows and columns to reduce size.
I filter my main worksheet to find the item type that I want to isolate - I copy the Result to another file - Run a Macro to Remove the information that I dont want to show. Hide Unused rows and Columns. Save as my File name and Upload to the Website.
BUT -- In my Excel Workbooks I have Macros - Some in the personal file. Some attached to Specific Files. When the Viewer clicks to see the file -- it offers the Macros ( Enable or Disable ). This Scares off many viewers and they Cancel.
When I delete the macros and save -- it deletes the Macros from MY computer as well.
How can I remove Macros from only the Sheet that I want to post for my website.
Here is the URL of one of the Current Buttons to a posted file if you care to view and see what I mean and am attempting to do.
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
I have developed a series of spreadhseets in which I use a macro to derive and insert the data from a SQL Server db. These spreadsheets may have one or more worksheets, some of which start as blank "canvases", others are preformatted with static data.
They are used for client reporting so are heavily formatted and include graphs; some manual preformatting, some coded, some graphs drawn completely by the code, some already in place - I just populate the data source. The VBA for the most complex one extends to around 5-6000 lines of code.
While this code does not add a huge amount to the footprint of the files, I feel that it is inappropriate for the spreadsheets to be distributed to the end clients with all this code included. The code is password "protected" but we all know how effective that can be although that is not really the point anyway - I simply feel it would be more professional if the code were not there at all.
There doesn't seem to be any options on a "Save As" method which enable the resulting sheets to be saved without the macros. I have tried recording a macro in which all I did was to delete the macros concerned - nothing was recorded. So a macro to remove macros seems to be out of the question.
These spreadsheets are held in a master folder on a server and are set as "read only". The users start the a sheet, run the macro (via a menu item which displays a selection form) and then save the resulting spreadsheet as another file in another location. I am wondering if there is some way I can cut into this process and develop my own "save" routine.
I would like to email an Excel file without the macros so the end recipients will only see the data and not be confused or excited about the prompt to enable/disable macros.
I'm just finishing up a project involving migrating an old Excel 4 macro to VBA. Unfortunately, now that my spiffy (and slow) VBA is finished, I can't find a way to delete the Excel 4 macro! I've tried going to Tools -> Macro -> Macros, and while the old macros are shown there, the delete button is greyed out!
transferring data from a worksheet (Passdown Report) to another worksheet (Data Base) located in the same workbook. In the source worksheet (Passdown Report) there are 2 cells (B2 and D2) in which I would like the data to be transferred along with the data from B4 to AQ33. All the cells contain a formula which I want to stay after the information is transferred to the target worksheet (Data Base). This will be a daily transfer to the target worksheet (Data Base), so the macros should also identify the next available open row to transfer the data to.
I have question about next worksheet, let say I have VB code that’s Run on sheet1, but Sheet2 to Sheet 7 al so needs after sheet1 finished, same format on at sheets.
I need to protect the attached to stop people deleting the formulas. The problem is that there are hidden macros and when i protect the cells the conventional way they stop working and i lose all the data in the IAS sheet. I have highlighted all the cells in yellow that i want to be able to change once protected.
At this stage I would just like some one to explain how to protect it. Ideally I want to do it normally (unlock the cells individually that I want to remain unprotected, then protect the whole sheet). If its not then presumably its a macro alteration that's needed?? don't do it at this stage as depending on how it will be protected will mean that i need to make a few adjustments.
I've been reading about usage of worksheet functions in macros, and I guess I'm still not getting it. What I want to do, is use Countif and Indirect worksheet functions in a macro but it always fails. Here is my formula
IF(Worksheetfunction.Countif(Worksheetfunction.INDIRECT("[DaysWorking.xls]'Collect No Stats'!$A:$A"),'2009'!Range("D" & x))>0)
Basically, I want to see if 2 different cells in 2 different workbooks match and then I would use the information to write different info in another cell. X is just a variable in a DO WHILE LOOP. Can these functions be used in a macro? Is there an alternative?
It's simple enough to delete all macros. I have a form that starts as a landing area for a bunch of data then it gets renamed. I want to delete both of my command buttons from the sheet and remove all of my macros. How am I going to pull this off, how can i save it to the sheet the first time?
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'cut command buttons ActiveSheet.Shapes("CommandButton1").Select Selection.Cut ActiveSheet.Shapes("CommandButton2").Select Selection.Cut
I have a worksheet "OPEN", and would like to apply both:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim i As Long Set rng = Target.Parent.Range("B:B") If Target.Count > 1 Then Exit Sub
[Code] .........
The first removes the rows if the validated column 'B' is equal to "closed", to a second worksheet "CLOSED". The second orders the rows by the same column, followed by column 'A'. Being brand new to macros, I've no clue how to make this happen...
I've managed to be able to create a macro to make a copy of a worksheet within excel.
However, when that copy is create all the macros are lost. If it is not possible to copy all the macros that are within the "Original Hours" sheet, I at least need a macro that will take people back to the original hours sheet (we have disabled sheet tabs, so need the macros to navigate through the worksheet).
Code used is below
Private Sub Hours_Click() Sheets("Original hours").Copy After:=Sheets("Original hours") ActiveSheet.Name = "Update hours" ActiveSheet.Unprotect "PASSWORD" With ActiveSheet.UsedRange .Value = .Value Sheets("Update hours").Select End With End Sub
Am a newbie in excel macros. pls i need assistance as to how to create a macro that searches a worksheet for a particular word. i wrote something like below but am having problems in it.
Private Sub CommandButton1_Click()
Dim word As TextFrame
If word Is Not Empty Then For word = "aaaaaaaaa" To "zzzzzzzzzz" ThisWorkbook.Worksheet.Find Next word End If
I have a formula which i need to paste into a worksheet, doing this is tedious and boring to say the least, problem is i am not sure how to get the macro to skip blank lines and only insert the formula where there are prices. Each week the worksheet changes so there is no consistent layout.
if you open a workbook & choose not to enable macros, is there any way to later change your mind & set macros to enabled without having to close & reopen the workbook?
I'm trying to avoid using macros in my workbook, however, I'm unable to get my worksheets to change name (based on a named range) without use of them, as follows.
I've written the macro:
Sub MyMacro() ActiveSheet.Name = Range("MyRange") End Sub
and then dropped the following into the worksheet Private Sub Worksheet_Activate() Run "MyMacro" End Sub
This works fine, but I'm trying to avoid using macros. When I try to place the following script in the worksheet private code, nothing happens:
Private Sub Worksheet_Activate() ActiveSheet.Name = Range("MyRange") End Sub
I have a file here that already has macros in it. The file is basically a excel document generator.
When you click create sku, the document will generate multiple documents based on the user inputs.
Two things in this file
1) In the generated documents the original "generator" file creates, I need generated files to have column B and D formatted to TEXT, currently all the generated files are formatted to general. (This code is in module 1, line 84 col 34.)
2) The other problem is I need to append the value in Cell N1 in the "sku data entry" sheet to be appended to the generated file names. (this part of the code is is in module 1, line 150 col 28.)
combine two Worksheet Change event macros into the one macro? The macros are listed below.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long
[Code]...
exitHandler:
Application.EnableEvents = True End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("O:O")) Is Nothing Then Application.EnableEvents = False
I have a complex sorting macro with userforms & Modules. Works great! Now I need to save the final Worksheet without the macros. The final step to my project is a Application SaveAs. Can I add a delete-all-macros step when the user clicks the Save As button which would create a new workbook with the finished worksheet and NO macros?
I need to copy my worskheet multiple times to create about 10 of the same worksheets within a workbook. I know if you click in the very most top left hand corner of the worksheet, then click copy, go to the new worksheet and click paste, it copies the whole page, but it does not copy over the VBA Code.
Also, for some reason, it does copy over my macro buttons, but does not put some of them in the right spot?
I have a macro-based employee leave system that works by couting the number of days shaded with a certain colour and thus calculating leave days taken, remaining and entitled... I have a single workbook with multiple sheets for different employees.
However, when I update by using ctrl alt f9, it updates all the other worksheets (ignoring their shading) with the values of the active worksheet! So if I update Peter, who has taken 14 days so far, it will update Liam's sheet too, with 14 days, ignoring Liam's actual shaded days...
How can I update each sheet individually, without compromising the other sheets?
I have a macros ( code inserted ) as you can see in my code the macros save the workbook in any file name you chose just by changing the any filename option.
I would like this to be changed so that it saves based on a cell reference, say i has a name in lets say B10 i would like it to save as the name in B10.
Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.DisplayAlerts = False Dim bk1 As Workbook Dim bk As Workbook Dim myfilename As String
Is there a possibility that there is a macro in the workbook even when there is no worksheet? I do not want a personal macro, instead, when I open a workbook(with no worksheet), goto tools->macro->open, I should see the macro. This would be helpful so that I can passover the macro to all my team mates and they can open any worksheet from there and work on the macro.
I have a workbook within which i have a worksheet that contains a lots of macro code (coded by me). As the workbook gets used by various people, i need to copy the worksheet and the macros across to the updated workbook, which doesnt contain the macro worksheet at all.
I have tried to copy it across by clicking on its tab and using the move or copy facility. This copies the sheet across as required. But for some reason, the macros all reference the old workbook. A small bit here for example for some reason opens up the old workbook and then performs the code in the old worksheet:
Sub SelectAll() For i = 12 To 20 Set curcell = Worksheets("Form Generator").Cells(i, 3) If curcell = False Then Cells(i, 3).Value = True End If Next i End Sub
As curcell is equal to worksheets...() i would have expected it to use the local worksheet, ie the one that the macro is attached to. So why is excel proactively hunting out the old workbook and sheet? is the method i used to copy across the sheet with the macros incorrect? If so, how should i go about it?
I have a worksheet with lots of things going on... the end user needs access to the Name field, DOB field, all the scroll and form control option buttons in the 9-question sheet, and the macro-enabled reset button at the bottom. Formulas are sprinkled throughout the worksheet, in columns T through AC... option button links go to AA and AB.
I would like to protect the entire sheet so none of the fields, except where indicated above, could be selected or edited... but when I protect the sheet, I get errors when trying to use you form controls, and the formulas do not respond. And finally, when protected, I get a debug error on the reset macro.
I have a workbook that I received from a vendor that when I click in a cell a comment appears. There is no line or comment indicator for each comment. So I went into the tools - options - views - comments section and chose no comments. The comments still appear and when I go to the tool bar - view - comments nothing appears. I checked for any VBA code and didn't see anything. I was able to get around this problem by copy/paste special into a new worksheet, but curiosity is killing this cat.
