I have a workbook which adds command bar named "Upload Processing" to Excel application if you open it.
The weirdest thing is, it hasn't got any macro stored anywhere or at least I can't see it anywhere (Alt+F11).
Note: Don't forget to use application.commandbars("Upload Processing").delete command from VBEditor to get rid of this newly added command bar when you are trialing.
First of I have a workbook, that runs several macros. All works fine. However I need to better secure the workbook. I realize that there is no guarantee on the security with excel but am hoping for something to solve the problem stated below.
When a user (with high security settings) opens the workbook in question, I would like the workbook to CLOSE out if the user chooses to disable macros when the asked by excel.
There are some excel documents in a hidden folder that contain sensitive information. Shortcuts to these documents will be provided to users to open them and add data. These documents contain all kinds of macros as well. Once macro disables the 'Save As' feature so that the workbook can not be saved outside of the hidden folder, preventing to some degree a user from saving as a different name and emailing the document with the sensitive data.
This all works fine with Macros enabled. As soon as macros are disabled the 'Save As' prevention is bypassed and the workbook can be saved anywhere.
Is there a way for an administrator to lock out the Trust Center options for disabling all macros?
I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i
I now have 2 working macros but cant seem to combine them correctly. How do I get them both into one macro so they run together.
View 2 Replies View RelatedI'm trying to do is delete a row of chosen cells in my form (form is locked with a password) using a macro then return back to the first cell below the deleted row. When I run the macro what's happening is that it's returning back to the cell chosen while writing the macro (which is at the very top A7). So if I'm several rows below A7, it deletes the row and then returns all the way back to the top of the form causing the user to have to scroll back down to where they were
View 3 Replies View RelatedAll my macros have suddenly started crashing Excel 2007 when I try to run any one of them. I can record a new macro but even that newly recorded macro will crash Excel.
Service Pack 1 was downloaded and installed by Windows Update two days ago and I had successfully run macros after SP1 was installed.
Today, it's a no go and I'm at a loss as to what may have caused this problem. I have not installed any software lately, other than the Windows updates on patch Tuesday.
This is on a Vista Home Premium machine.
When I try to run a macro, Excel crashes with this info:
why my Macros have suddenly stopped working for no apparent reason?
For Example - one macro would run when a Cell in Column A was selcted. It would trigger a macro to save the workbook.
I'm not giving details of what the specific macros do as it's more of a question as to why macros in a workbook just suddenly cease to function.
I have a macro to collate data from multiple workbooks to a single sheet. The folder path to the source files can be chosen thorugh the macro. It runs perfectly in my system running on Windows XP. But it doesnt run on Wondows 7 system. Both are using MS Excel 2010. In Windows 7 system, while choosing the folder path where source files are located, it says "No items match your search" (screenshot attached). I would want the macro modified so that it runs on any platform. The macros that I'm using is given below:
[Code] .....
Untitled.png
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 recently had a virus on my computer and had to replace it. I had a ton of macros saved in my Personal.xlsb workbook, so I exported those modules and have now imported them to the new Personal.xlsb file. Now for some reason those macros no longer work in other files. Most of them are basic, like I have a macro to paste values or paste formats, etc. but I have some others that are very complex. The macros work fine as long as I am in Personal.xlsb, but if I try to use them in another open workbook I get the "all macros may be disabled or the macro may not be available in this workbook" message. I am using Excel 07 and have verified that macros are enabled.
View 3 Replies View RelatedI have created a workbook ( with protected sheets) containing two macros " To Insert a Row ( Copying the formula from the row above) and "Delete a Row", both, assigned to a customized toolbar.
when I move the workbook, say from the folder where I created it originally to the desktop, the macros stop working giving error like THE MACRO " C:so n so... CAN NOT BE FOUND!
Is there a way using VBA that will allow me to disable a COM Add-in when I open a workbook without me (or the user) going through the steps to remove it first.
The reason for my asking is this: We have installed SAS Add-in 2.1 to Excel 2007. So this brings up SAS under the Add-Ins tab. But then when my program executes and it sees I use command bars, it adds my command bars under the SAS menu. I am trying to make the interface as clean as possible for the user, so they only see my command bars or menus.
Also, if we were to install the SAS Add-in 4.2 to Excel 2007, I have read in a SAS paper that SAS Add-in 4.2 creates its owe tab on the ribbon instead of inside the Add-in tab. So I would be able to use xml to make the SAS tab visible="false".
Is there a way to disable alt+f11
I will only disable it if the file is opened as read only
and I will enable it on before_close.
I am just using a simple sheet to keep track of email addresses. Every time I enter an email address it automatically creates it as a hyperlink/mailto function. So if I click on the cell, it will create a new email via outlook addressed to that person.
This is driving me nuts! I have a few thousand emails, mostly copied from other people's sheets, and 90% of them are set like this. Is there a fast way I can disable this function so that every cell is just text?
is there any way of disabling the following functions:
adjusting the size of the horizontal scroll bar;
being able to scroll to the right to search for tabs covered by the scroll bar
the reason being I want to have a few sheet tabs hidden but still be able to see the data on the sheet if a button taking you to the sheet is clicked
Private Sub cmdCalculate_Click()
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub
Is there a way to disable the F9 Calc key and get a user to hit this command button?
Right now this code does not work. I do have Calculation set to Manual so it is disabled in the worksheet.
Is there anyway in excel to make it so that you cannot copy the data off of the spreadsheet, but the user can still do functions, more specifically filters and such.
I've been asked to put it in PDF format, but my spreadsheet becomes useless without the use of filters. It's a check for the copyrights of my company.
I'm working on a workbook that has alot of calculations in it, on a pretty slow PC (Work-related), so auto-calculate is casuing crashing, so what I've done with this sheet is disabled auto-calculate and added a macro to calculate each sheet indiviually instead, which avoids the crashing (The last sheet still takes a minute and a half to process but thats ok I guess).
My worry is, when this workbook does the rounds, peoples excel will already have autocalculate on, and will crash it themselves. Is there anyway I can ensure that the sheet turns atuocalculate on or, failing that, does anyone have any ideas on how to help the issue?
I'm looking to disable the horizontal and vertical scrolling in 1 worksheet
Am i able to do this?
I have a UserForm with lots of controls divided by multiple frames. (163 controls over 19 frames). The same UserForm will be collecting multiple sets of data but the first set is a "master" set. Anything entered in the master set will auto fill in subsequent sets.
What I need is a way of preventing the user from changing any options that were input in the master set in subsequent sets. ie, If I entered info into Frame1 of the master set it would show up the next time I opened the UserForm (I have this part), but I would not be able to interact with it (this is what I need).
Since I'm already pre-populating subsequent sets with the master info, my first thought is to loop through each frame testing for input and if so setting Enabled = False, but I'm not sure how to do the loop/test. Is there a way to set up a "For Each Frame in UserForm" statement? Also, the controls in each Frame could be TextBox, OptionButton, and/or CheckBox. (sometimes more than one type in the same Frame)
I have a userform with one combo box and one check box. Using the below script I am attempting to disable the check box as soon as a selection from the drop down option of the combobox is made. The combo box drop down menu starts with a blank. What I am finding is that even if the blank option is selected then this disables the checkbox prematurely. Ideally the checkbox should only disable if a non-blank option from the combo box drop down menu has been selected.
What I would like to have as end-result is that the blank option from the drop down menu in the combo box can also enable the check box again if a selection from the drop down menu has been chosen but then reverted again.
Code:
Private Sub ComboBox1_Change()
Select Case CheckBox1.Value
Case True: ComboBox1.Enabled = True
Case False: CheckBox1.Enabled = False
End Select
End Sub
I have a template sheet where the only thing the user need to do is fill in all the appropriate cells and the click the button that says SAVE to save the file to the right folder and file name.
I want to Grey out the ribbon completely so that user can even change the font or any thing else for that matter. The sheet and workbook is protected. Need a way to stop any unwanted tinkering.
I ran into a problem with one of my macros the other day. It processes several data files given to us each week. This week, one of the files had Auto-Filter enabled. I had to manually disable auto-filter, and re-run my macro.
I tried recording this action, but wasn't able to. Is there a way to disable/enable Auto-Filter with VB?
I have recorded a simple macro that refreshes a set of random data in my sheet everytime I click on the button I made.
When I see a zero in one of the cells I know to stop refreshing. The thing is, I click on the button quite rapidly and sometimes miss the zero. I need to click on this button quite quickly so slowing down isnt an option!
What I need is for excel to stop that button operation when it sees a zero in one of the cells.
How to disable by code Maximization of Workbook?
View 2 Replies View RelatedI'm working with Excel 2000 on Windows XP machines. I have the following
Private Sub soInputBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
PushScreenUpdateState False
clearAllData
loadAllData
Worksheets("Main Screen").Activate
PopScreenUpdateState
End If
End Sub
Private Sub loadAllData()
soInputBox.Enabled = False
End Sub
The idea is this: the user can either type the value into the text box (soInputBox) and then click a button, or type the value into the box and then hit enter. They will both do the same thing; set in motion all the functions that load a bunch of data, etc.The code for the event handler for hitting enter is shown above. Inside of the loadAllData function, I disable the text box. What seems to be happening is that after the soInputBox_KeyDown function exits, Excel crashes. If I comment out the line to disable the text box, Excel doesn't crash. My question is this: is this a known problem, and is there a known workaround? I would like it so that the text box is disabled so that the value that was input cannot be changed accidentally during the rest of the programs execution, so if there's a way to change the control to be unmodifiable instead of disabling it that would be a reasonable alternative.
I have 3 cpmmandbuttons in a spreadhseet named "Controls" . viz
Trim and Edit Base DataCreateReportArchive
They are always run in the same order.
My Aim is to disable and gray out the Archive button once its assigned macro is run(by clicking it) so that it activates only next time that the workbook is opened.
I noticed the arrow keys will move the active cell while I have an inputbox active. This will be an issue for my users, I need them to key in data only.
Is there a command that will disable the arrow keys while the users enter the requested data?
I need to find a way to do conditional formatting and disabling of a cell based on a value from another cell. For instance if A1=X,Y, or Z, then B1 will get grayed out and cell entry will be disabled. This is so that when skimming over the sheet, you can tell what cells in row B you need to put info in still, and if it's grayed you'll know you don't have to put anything there (and it won't let you).
View 14 Replies View Related