I update an Excel file daily and save it to a server on a network. Other network users access this file from their own computers. I have a macro created that opens a dialog box upon opening that shows users know of anything important.
Since employees in the office have Excel set to Paranoid mode or something like that, they are never even prompted that macros exist and are intended to run and that they should choose to allow them to run or not.
I would like to approach the problem in a way other than asking all employees to activate all macros or to lower their Excel security settings at all times or only when opening this single document. That would be considered too inconvenient.
I need to write a macros that will activate another macros whenever the cell "I4" is modified. So far it is not working the way it suppose to. Maybe anyone can spot a mistake? Here are both Macros that I have: ....
I have used the following Macro that deletes the Macro code. It then saves the file and quits excel.
************************************* Sub removeAllCode() Dim awi Dim awcl As Integer Dim count As Integer Dim i As Integer 'loop counter On Error Resume Next count = ActiveWorkbook.VBProject.VBComponents.count For i = 1 To count Set awi = ActiveWorkbook.VBProject.VBComponents.Item(i) awcl = awi.CodeModule.CountOfLines awi.CodeModule.DeleteLines 1, awcl Next i Set awi = Nothing ThisWorkbook.Save Application.Quit End Sub *************************************
However when I open the Excel File, I still get the Macro Prompt. I have checked in Macro list as well as in VB Editor and no Macro code exists
If I have the file manually (or do a save as) and then reopen the file, I no longer get the Macro prompt.
I have tried various examples listed in different forums/sites but nothing works.
I have created some macros and assigned shortcut keys. The workbook is shared by other users. I want to keep these macros from being accessible to other users. I know that they can go to ToolsMacros and display the names of macros and run them. How can I prevent them from accessing the ToolsMacros? Or can I assign password to individual macros?
If I put the macros in individual sheets as Private, they don't show up on the ToolsMacros list, but then I can't assign shortcut keys.
I am designing (badly!) an excel file that is intended to be as user friendly as possible. I would like to add in a navigation feature using a list box or other appropriate control form that will take the user to the correct tab in the workbook depending on which item they choose from the list.
My aim is too hide all the tabs at the bottom whilst not crowding my page with multiple command buttons, and still be able to navigate easily through the workbook.
I have been looking at Dave's Script found in this post Determining if macros are enabled It works just like i want it too. But when I try and use it in my workbook with differnt sheet names it doesnt work. I have even tryed to rename the sheets in the example file and it stops working. My sheets are as follows:
Sheet1 (JI) Sheet2 (GC) Sheet3 (ET) Sheet4 (SV) Sheet5 (JB) Sheet6 (P) Sheet8 (Info Sheet) <----This is where the Notice to turn on Marcos is Sheet9 (D)
I have the following code in Module2
Public bIsClosing As Boolean Dim wsSheet As Worksheet Sub HideAll() Application. ScreenUpdating = False For Each wsSheet In ThisWorkbook.Worksheets If wsSheet.CodeName = "Sheet8" Then wsSheet.Visible = xlSheetVisible Else wsSheet.Visible = xlSheetVeryHidden End If Next wsSheet Application.ScreenUpdating = True...................
I have a workbook that I would like to share and allow multiple users to add and make changes at the same time. However, the workbook is full of macros that really need to run. Is this possible? The workbook is written in excel 2003.
If this is not possible then is it possible to share the workbook in a similar fashion but not allow any other users to make changes, but update their workbook with the changes that I make?
I have a sheet with macros that allows users to fill in data from columns F10 to O10 from row 10 to 54.
Column P will then make some computations based on the inputs from col F to O. Row 55 will also make some computation based on inputs from row 10 to 54.
How to I modify the code below to allow users to either add/delete columns and rows such that it does not fowl up the computations?
The computation does not have to be at column P or at row 55, they can be dynamic and change depending on how many rows and columns the users delete/add.
A little background on what the code does. From Column F to O, the code UNLOCKS the columns only if user inputs date in Cell 3 of that particular column.
Example. Column F is locked from F10 to infinity at the start. Only when user inputs date into F3 does the cells F10:F54 is unlocked for user to input data.
This goes on until column O. I want users to be able to add more columns, or delete ones they don't need.
I am using Reafidy nice bit of code to hide worksheets if the user disables macros. The problem, though, is that it doesn't work for Chart Worksheets. How should I adjust this to accommodate charts?
Private Sub Workbook_BeforeClose(Cancel As Boolean) bIsClosing = True End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim wsArray() As Variant Dim iCnt As Integer Application. ScreenUpdating = 0
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 a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
In column L on sheet1, I want the user to type in a number. That number will be the number of recommendations that need to be done.
Once "L" is populated I would like the user to be prompted to fill out -what the recommendation is -date in should be finished by corresponding to the number that was typed into "L" which would be listed onto a new sheet. So if the user typed in 3 - I should have 3 recommendations with dates.
On my new sheet, I would like "A" to match "A" from sheet1 for the first one only "B" would be the recommendation from the prompt "C" would be the date of that prompted recommendation
Is it possible to prompt the user for a specific row number to delete in a vba textbox? If yes, then the code would fire using a button. I have the worksheet protected and only want to unprotect through code to delete a specific row number, then protect it again.
I'm in search of a way to prevent people who use a spreadsheet of mine from screwing up formulas. I've hidden rows 10 and 12 in a spreadsheet. i have a TOTAL formula in a cell on row 13 that counts from 10-12. that way, when people insert rows, the total is always right. problem is, people somehow seem still screw it up.
I'm hoping there's a way for me to put a button on the sheet to insert rows. and when the button is pushed, be promted to enter a number of rows. then have it insert that many rows.
I've not used Input Box function before. When a user opens my workbook I want to check whether a named range ("Name") is blank and, if it is, to prompt the user to type their name in an Input Box which then returns that value to the named range. This is what I have tried but I don't think I am referring to the named range correctly,
Dim strName If IsNull("Name") Then strName = InputBox("Please enter your name", "Name Required", "1") Range("Name") = strName End If
I have created an addin that runs several macros. To enable the user to undo any changes, I keep a backup prior to them using any of the macros in the form of a worksheet named back00xx. I want a prompt to be displayed to the user to remove these backups at the closing or saving of the file. The problem is that as an addin, how do I get it to display this prompt as the open workbook is not part of the addin.
Could one have a looping routine that is checking every xx min/sec to check if the current workbook contains any sheets starting back00
Currently I have information that includes numerous distributors information on one sheet. The code below separates each distributors information into a new tab and a new workbook, names the workbook and saves it in the specified file path.
I am trying to figure out a way to use an InputBox to popup and ask the user where all the new workbooks will be saved at. Thus, avoiding the need to change the code each time you want the new workbooks saved in a different folder .....
After recording a macro that goes to last figure in data, how do I edit this to prompt for a new data value and enter it into 1st empty cell at end of data?
is there a way to make a macro that runs on startup and prompts with a yes/no question, if the user wants to multiply all new data values time 1.0825, and after having selected if they do or dont want to allow that, would it be possible to have the prompt minimize itself but still be present in case they decide to toggle that on/off?
My Excel File Name "eai.xls" ,when I Click The File , It Open The Sheet Name "menu" With The Inputbox Prompt:="put Your Id" When The User Put The True Id , It Will Show The Input Box Prompt:="your Password" . If The Password True Will Activate The Sheet Name "data" .and Hide All The Menu Bar.
If The Wrong Id Name Show The Msg.box "re Enter Your True Id " ,that Also For The Wrong Password In Which If User Put More Than 3 Times Wrong , Will Close The Workbook And Exit File.
The User Persons Allow 10 Person Id . Keep In The Table Of The Sheet Name " Id And Password" The Id No. Are In The A1:a10 Password No. Are In B1:b10 This Sheet Name Is Hide And Protect
I run a macro to open multiple workbooks and run a macro within those workbooks. All worked fine until a message/input box was included in the individual workbook macros [that I cannot change] to notify the single workbook user when the macro had successfully completed and prompts for an “ok” input.
Can I get my macro to ignore this prompt and not wait for an input – I am currently clicking “ok” everytime it comes up on screen. Have searched help and forum but nothing jumps out.
I have eventually got my Worksheet up and running but now im trying to make things a little easier to complete. The plan is when the Engineer is on site he has to open the Excel sheet to check the tests he has to complete and then mark the results. this is fine but for vaildation he has to sign everthing etc... i made it easier so they only had to fill in one box and it would complete the rest but this seems to get missed more often than not.
SOOO
i want a Screen to pop up when some one opens my excel that says Enter name and contact details. then they submit this which updates the spread sheet with this information and then the engineer can continue as normal.
Is there a way for VBA to prompt the user for a new selection?.
For exmaple,
Code: Sub BlankRow()
The reason I need this is because I am already in a macro and can not change selection on screen and I don't want to exit routine to make new selection (last line of code).