Hi All - I've got a project I'm working where I'm trying to disable the user from turning on Design mode. I'm trying to make sure I can run WorkbookBeforeSave before any save, but the user can bypass BeforeSave if they go into design mode before saving. I found a useful solution in an older post on these helpful boards, but am now seeing some weird behavior I'm having trouble fixing. The solution I found was to add code to ThisWorkbook module like the following: Code: Private Sub WorkbookOpen ' Remove Design mode buttons (design mode disables macros): Application.CommandBars('Visual Basic').Controls('Design Mode').Enabled = False Application.CommandBars('Control Toolbox').Controls('Design Mode').Enabled = False On Error Resume Next Application.CommandBars('Exit Design Mode').Controls('Design Mode').Enabled = False On Error GoTo 0 ' More code here. End Sub Private Sub WorkbookBeforeClose(Cancel As Boolean) ' Re-enable Design mode buttons: Application.CommandBars('Visual Basic').Controls('Design Mode').Enabled = True Application.CommandBars('Control Toolbox').Controls('Design Mode').Enabled = True Call HideSheets ActiveWorkbook.Save End Sub The code in WorkbookBeforeClose is so that the user will have access to design mode again once the file is closed.
This seems to work fine, except I've found some weird behavior that I can't find a work around for. If I take the following steps: 1. Close the file 2. Open the file again but, when prompted, choose Disable Macros, then The file will open with the 'Exit Design Mode' toolbar floating in front of the worksheet. That's slightly annoying, but not my real problem. If I then: 3. Close the file again 4.
How do I change the name of a command button in Excel. Ask Question 11. I feel like such a dunce, but I cannot figure out how/where to do this. However, when I click Properties (to the right of the Design Mode icon), then click the button, it doesn't show me properties for the button. Instead, it shows me properties for Module1.
Open again, this time Enabling Macros, then The 'Exit Design Mode' toolbar is once again floating on the screen and I can use it to go into design mode which defeats the purpose of the code. If I just close the floating toolbar, then the 'Exit Design Mode' toolbar is no longer available from that point forward. I tried to add the code: Code: On Error Resume Next Application.CommandBars('Exit Design Mode').Controls('Design Mode').Enabled = False On Error GoTo 0 to address the possibility of the floating 'Exit Design Mode' toolbar being there on open, but the code seems to have no effect. I'm using Excel 2003 on WinXP. Any one have any ideas how to disable 'Exit Design Mode' Toolbar in this scenario? Any suggestions much appreciated! Thanks, Dave.
I'm having a problem in a workbook with several ActiveX command buttons. I had been using the form control buttons to run macros, but the boss wanted each button to have it's own, different color. So I removed the form control buttons and created new ActiveX command buttons. I got into the button properties and set the background colors. I added the Click code to run the macros when the user clicked the buttons. All of the buttons were working fine.
Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click.
No error message. If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties. I can't seem to locate the command button properties any longer. I still see the button name 'cmdButtonGetInfo' and '=EMBED('Forms.CommandButton.1',') in the name box and formula bar.
The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work. It's like the buttons are being disabled when I close or open the file. Any suggestions? I did a bit of browsing on this problem.
Found others suffering the same but haven't found any conclusive answer yet. Every so often when I attempt to save a file, (including save as), Excel won'r let me. By won't let me I mean: using Save doesn't appear to do anything using Save As doesn't either do anything, the dialog is not displayed and if I am doing via the File menu then the File menu is exited and the previous ribbon tab is displayed (i.ethe one I was on before clicking 'File') if I close the workbook I am prompted to save, close without saving or cancel. Clicking save just invokes the same msgbox again. I can't work out when it goes into this mode. Some days I can work without this problem, other days I encounter this 2 or 3 times.
The only thing I could suspect was I think this started around about the time I installed xlDennis' code library. I have uninstalled the addin and so far so good, but I cannot categorically say that this was the cause. Anyone have any idea? Cheers Jon Edit: I have read this: Doesn't seem to cover the issue I describe. I have a problem sometimes.
I will click on a cell to add information. The cell turns blue and then wherever I move, it highlights those to. No matter where I go on the page. If I Alt-Tab and work in another program on my computer, that excel page keeps highlighting wherever I move even in those other programs (I know this sounds confusing).
When I return to excel thousands and thousands of cells are blue. The biggest problem is that the highlighting won't turn off, no matter what. I can't select anything from the tool bars, do any work on the sheet or close the program. I can close it only with the task manager but when I open it again, the cursor is still stuck in the highlighting mode and won't perform any other functions. Do you think this is a problem with my computer, the excel program?
I have changed my mouse and this didn't help. Is there some shortcut to turn off this highlight feature other than restarting my computer. Which is the only current way I can get rid of it. Thanks for any advice. I am using the code below to disable the save function very successafully.
However, is there a work around to allow a macro to save? - Private Sub WorkbookBeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'this disables the save function on the XLS MsgBox '.Save is Disabled.' ' Following line will prevent all saving Cancel = True ' Following line will prevent the Save As Dialog box from showing If SaveAsUI Then SaveAsUI = False End Sub. Hello, I have a number of different files that I often need to run a macro on. In order for me to do it on the 75-100 files I have at any given time, I need to open one, run the macro, close and save, then open the next one.
Is it possible to write a macro that will start with the first file in a folder, open it and update links, run a macro, save and close, and open the next file in the folder until it has open all the files in the folder. I have experience with creating macros that reference different workbooks, but not sure how to go about opening files with different filenames (without referencing the exact filename). I'd like to be able to have basic code for opening, saving and closing, opening next file, saving and closing, etc. And input the macro I'd need to run in each file in the appropriate location. Is this possible? Any help is greatly appreciated!!
Thanks, Jason. I'm trying to open a pdf file from within excel vba. I have tried using the followhyperlink method but adobe acrobat opens very briefly then immediately closes Code: Sub OpenPDF 'Dim pdf As String On Error Resume Next 'pdf file to open pdf = 'K: PDF mypdf.pdf' 'open the pdf file ActiveWorkbook.FollowHyperlink pdf End Sub So then I tried to create an instance of acrobat by setting a reference to the acrobat object but I can't get this to work either! The code I'm using is Code: Sub OpenPDF Dim pdf As AcroPDDoc Dim strPDF As String Set pdf = CreateObject('AcroExch.PDDoc') 'pdf file to open strPDF = 'K: PDF mypdf.pdf' 'open the pdf file pdf.Open strPDF End Sub Any ideas what could be wrong with either approach? Hello, i've got the following problem: I want users to double-click on a row on a protected sheet and then do some code based on the row-number of the clicked cell. I've protected the sheet because it contains a lot of formula's.
When a user double-clicks a row it triggers the code through the WorkbookSheetBeforeDoubleClick event. After the code is executed Excel shows a message that the cell that was clicked was protected etc etc. How can I prevent this message from popping up? I've already tried Code: application.displaywarnings = false but that didn't work Thanks. I'm trying to use VBA to go to a website that requires a User Name, Password, and a Submit Button.
So far I can get everything to work besides the Submit part. The code runs without errors, but doesn't actually 'hit' the submit button on the webpage.
For posting, I removed my actual user name and password and and used the generic ' User Name ' and ' Password ' highlighted in blue. I highlighted another section in green that I took from a previous post hoping it would solve my problem. Thought I'd start this topic since there seem to be a number of topics where the answer seems to be to use one of the above rather than other. Thought I'd kick off with my 2 cents' worth. I have a userform with frames containing textboxes. The user enters a currency value and once they leave the control, then a protected textbox next to it shows the corresponding value in SEK.
I started off using the exit event but ran into 2 problems. If you tabbed out of the last textbox in the frame, the exit event never kicked in (this is documented in other topics but took some time to find). This resulted in me using the exit event for all except the last textbox in the frame that used afterupdate instead I then discovered that the exit events didn't kick in if, instead of tabbing out of the field, I deliberately placed focus in a control elsewhere on the form.
Changing the event from exit to afterupdate corrected this. My question then is. Could you guys document in this topic when you would/must use the exit rather than the afterupdate event (or vice-versa). I know this question has been asked a bajillion times, so I apologize for the redundancy. I am working with an Excel spreadsheet and saving it as a.csv file in order to upload to an application that parses out the.csv data as transactions. The system requires.csv files, so this is how I need to save my doc (with this extension).
I have been successful at preventing Excel from coverting that long number into scientific format. I have saved as a TXT file, pasted the longer number and it displays correctly.
That is all good. But I have to save as a.csv. So if I do that, close the Excel window, and then open again (as the.csv file), the numbers are back to being displayed in scientific format.
I have tried creating an Excel doc from scratch and entering text in Text format, to see if this created a cleaner file. But again, the second I save as.csv, close the window and then open that file up again, that dang scientific format is back. Does anyone have any idea of how to work around this? Once I have successfully gotten the numbers to display as the long-chain number, how can I get them to 'stick' so that they don't revert back to scientific format when I reopen the file? Thanks so much for your help!
I have searched and read all the help files. I find the properties of an object, I see how I can 'lock', 'size and move with cells' or 'not move with cells'. No matter what I select, the object moves off the screen, when the user, scrolls to the right of the spreadsheet. Is there a way to lock the position, let's say, in the upper right corner and have it stay there?
This would be quite useful for an EXIT button, that I have created, that will close the program without saving (it's a read-only file.) Thanks to all the wonderful people here that have been so helpful and give us their valuable insight and time. Hi guys, Looking for some help and would appreciate your help. I want to prevent people cutting/copy/pasting on a spreadsheet I have developed.
A couple of users keep doing so which in turn knackers my formulas. There will be occasions where I will need to be able to use these functions for maintenance and updates.
Ideally I would like have a private marcro which I can run to enable these features as and when needed. I've been at this all morning and feel like I'm going round in circles. I've tried various bits of code (sourced via google etc) & have ended up with a whole manner of outcomes but not the one I want. To summaraise what I am looking for is some vba code which will do the following Disable cut,copy and paste when sheet is opened Enable cut,copy and paste when closed Have a macro which when run will allow me to cut/copy paste so as to implement updates when necessary. Can anyone help?
Thanks in advance. I have workbook in which I want to save a specific worksheet to a new file with only the values saved - all data in this worksheet are references to cells on another worksheet, which is using VLOOKUP to pull data from a database. Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference. I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN). What modifications do I make to this to get this to work per above requirements?
Sub CopyMe Dim SaveMeAs As String SaveMeAs = Sheets('Sheet1').Range('B2').Text Sheets('Sheet3').Copy ActiveWorkbook.SaveAs Filename:='C: My Documents ' & SaveMeAs End Sub. Hi all, I've had a long search through your pages to see if this question has been answered before but having browsed through about 50 pages worth of threads I couldn't see anything, but if I am repeating prior information I do apologise. I've written a macro that is relatively simple. It just takes some information in one format, rearranges it, adds some formatting and performs some calculations.
Nothing incredibly fancy but it works fine on my computer. Now, I need to share this macro with some other people, so basically I've just sent that excel file on to the people that need to use it. Should be fine and in most cases it is, however there is one user who although they can open the file, can't seem to get the macro to run properly.
It seems to get a small way through the macro but then stop with no error messages or any sign that it hasn't completed properly. I have checked Macro Security level and that is the same as mine, Tools - Add-Ins is the same, In Visual Basic, Tools - References is the same as mine. It is the same Operating system and the same version of Excel. I have even signed into this person's computer as myself (it's a big company network thing) and tried to run the macro and it works fine, so there is nothing wrong with the hardware. I've googled and searched and tried everything I can think of but I'm no closer to solving this problem, so if anyone has read through this wall of text and can come up with a possible solution, that would be greatly appreciated to save me from tearing ALL my hair out!
Thanks very much for your time. Is it possible to share a workbook with macros and have the macros work properly. I have one spreadsheet that needs to be shared by many users. We want to all be in the same document at the same time saving changes. The real catch is that the workbook has macros. I used the 'Share Workbook' function under tools. At the end of the setup, it told me that the macros would not work properly.
The odd thing is that everytime I try to click on one of the macros I get an error, but yet the macro still performs the function. So essentially the macro works, it is just a pain because you have to click 'end' everytime on the error screen. Any suggestions?
I am trying to insert a range of cell in the body of an outlook email with the same format. The code I am using now is below and it does insert anything in the email body. Code: Sub Mail Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject('Outlook.Application') OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail.To = '[email protected]'.CC = '.BCC = '.Subject = 'Burden Report'.Body = ActiveSheet.Range('A1:D12').Attachments.Add ('C: Documents and Settings cbelcher Desktop Burden Report.xls') End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing Set objMsg = Nothing End Sub.
Hi, I'm trying to get some macros to combine data from two open workbooks, and I can't figure out the commands to switch from the active workbook to the other open workbook and back. I'm new to VBA and just learning the ropes. All I can figure out is how to switch to another workbook with an exact name.
If I record the macro to go to a recent file, for example Window 1 (filename), the code that I get in VBA is Windows('filename').Activate. This doesn't translate to when I have two different workbooks with different filenames open. I'm a bit confused myself. Maybe just a list of basic workbook-switching techniques or commands would be useful. Hi there, I have a piece of code called ConvertDates that formats data contained on 6 worksheets. The 6 data sheets all contain a data connection to a website of foreign exchange tables.
What I want is for my code to execute as soon as the data connection refresh has finished. When I use the statement Code: ActiveWorkbook.RefreshAll Application.Run 'Project1.xlsm!ConvertDates' The code executes the macro whilst the refresh is still happening, thereby screwing up my results. I don't really want to use a timed wait, because the refresh speed is going to vary from user to user.
Is there some way I can tell excel to wait till the refresh has finished and then execute the code? Any help would be hugely appreciated.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |