Putting Enable Macros Button Into Userform Type Display
May 12, 2013
In reference to my other topci here: Userform Error
In this workbook I have successfully made it act like a userform calculator, the user does not ever see the actual sheets in the workbook, their only interface is the Userform calculator I created.
This is exactly how I want it, they do not need to see the sheets, nor do I want them to.
This leads me only to my next problem... Some of the users obviously wont have Macros automatically enabled, what I am wondering is, is there a way to put the normal "Enable Macros" button which appears along the top of a macro containing workbook when opened, I want to put this button into a userform like display without showing the workbook at all.
So here the process I envision:
1. User double clicks the file
2. It opens and automatically goes invisable (like it does already)
3. If the user does not have macro enabled, a userform like box appears and says "Please enable Marcros to user" with a button below for "Enable" and "Exit"
4. If exit is pressed excel closes
5. If enabled is pressed Macros are enabled and then they will automatically see my calculator userform like normal.
As you see, at no point do I want them to see excel or a workbook, just userforms so its not like they are using excel at all.
I have searched around the net and found a few ideas to tackle this, mainly one that has a "Prompt" workbook that shows when macros are disabled, but that is not ideal for me. However perhaps I could just edit this "prompt" code thing for my situation?
Here is the prompt sheet code I talked about:
Code:
Option Explicit
Private Sub Workbook_Open()
With Application
[Code]....
How to use:
Open an Excel workbook Select Tools/Macro/Visual Basic Editor in the VBE window, select View/Project ExplorerSelect the This Workbook module Copy and paste the code above into this ModuleNow select File/Close and Return To Microsoft Excel Don't forget to save your changes...
8. Name one of your sheets "Prompt"
View 2 Replies
ADVERTISEMENT
Dec 5, 2009
I have a User Form which i want to use in such a way that editing the worksheet directly is not disabled while the userform is running. I would also like to use the right button mouse menu(cut, cpy,paste) while i am using the user form. I am using two Monitors with my PC and i want to work with User Form on one monitor being able to edit worksheet without having to close my User Form.
View 2 Replies
View Related
Jul 28, 2008
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?
View 9 Replies
View Related
Feb 12, 2010
Is there any code that will automatically enable macros on Auto_Open?
View 6 Replies
View Related
Apr 24, 2009
On several of my worksheets, I'm asked if I want to disable/enable the macros but there are none associated with that particular worksheet- matter of fact, none of my worksheets have macros unless pivot tables count, so why am I being prompted in that way?
View 9 Replies
View Related
Oct 23, 2006
I am trying to use the EnableMacros. zip script located here {url} But I am having a hard time making it work!
1. I have copied the module in EnableMacros.zip and imported it into my workbook.
2. I have placed identical code in my workbook's ThisWorkbook as is in the EnableMacros.zip's ThisWorkbook.
3. I have copied Sheet1 from EnableMacros.zip titled "Info Sheet". In MY workbook it is sheet7, but it is titled the "Info Sheet" in the EnableMacros.zip.
4. I have also copied sheet6 from EnableMacros.zip into my workbook and titled it "LoadScreen". In EnableMacros.zip the sheet is titled "Sheet1"
...
View 4 Replies
View Related
Sep 10, 2009
I have a couple spreadsheet Excel 2003 that I have to Email, I use a few macros with these spreadsheets. The Macros are kept in my personal workbook. When I Email these sheets does the end user see the warning enable macros or do they just see the sheet as is. I am mailing the whole workbook but macros are in my personal workbook. If they see the warning is it possible for me to Email so they will not have to contend with that?
View 3 Replies
View Related
Apr 17, 2008
Whenever I open an Excel file that has macros in it, a message always pop up and ask if I want to enable or disable the macros.
Is there a way to get rid of the message?
View 9 Replies
View Related
Jan 16, 2010
1) Force user to enable macros.
2) keep three worksheets very hidden all the time.
3) passwrd protect vba
I have been using this code to force user to enable macros but this unhides all sheets other than "macros not enabled" sheet. I need three worksheets to remain very hidden all the time. How do I do this.
Option Explicit
Dim ws As Worksheet
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Macros Not Enabled").Visible = True
For Each ws In Worksheets
If ws.Name "Macros Not Enabled" Then ws.Visible = xlVeryHidden
Next
End Sub
Private Sub Workbook_Open()
For Each ws In Worksheets
If ws.Name "Macros Not Enabled" Then ws.Visible = True
Next
Worksheets("Macros Not Enabled").Visible = xlVeryHidden
End Sub
View 9 Replies
View Related
Jun 14, 2008
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...................
View 2 Replies
View Related
Feb 9, 2007
the system i'm creating involves many user ... some may know how to decode the system esp. on the startup ... if they click disable macro they can view, edit and delete important data in the system ... can somebody guide me on how to automatically enables macro on excel file on startup???
I've tried:
- setting security to low - but got auto reset by administrator.
View 2 Replies
View Related
Mar 10, 2009
I have a spreadsheet with 2 combo boxes - Combobox 1 enables a user to specify if they want to look at the data on the sheet by week or month - it has just 2 options - "select by week" or "select by month"
The list in combobox 2 changes based on the selection in combobox1 - so if you select "select by week" , a date list by week shows up.
Combobox 2 is supposed to run a macro which updates some backend pivot tables. there are 2 types of backend pivottable - weekly tables and monthly tables.
What i am not sure about is how to get the selections in combobox 2 to run either the weekly macro or the monthly macro based on if combobox1 is set to week or month ...?
View 9 Replies
View Related
Apr 7, 2014
So I have a Workbook that I want to make read-only/protect after a certain date. I also want to force the user to have macros enabled. So far I have the following script, but despite changing it to read-only it doesn't seem to do anything. I have also tried to enable protection on the sheets after that date, but am getting errors.
View 2 Replies
View Related
Jul 14, 2009
What is the code to enable macros upon opening a workbook and avoid receiving the message "Security Warning-Macros have been disabled" in Excel 2007?
View 3 Replies
View Related
Mar 13, 2012
I have been using Erik's fantastic "Force Enable Macros" code (exactly as it appears)...
force enable macros & ASK to save changes (SOLUTION)
...with the desired results (and comfort) under Excel 2003.
The code works under Excel 2007. However, if there is another workbook open at the sametime (regardless of whether Workbook two has macros or not), I get an Excel "crash";
Code:
Microsoft Office Excel has encountered a problem and needs to close.
[ ] Recover my work and restart Microsoft Excel There is also a Event ID 7001 in the Event Viewer. I have run Excel diagnostics and there are no issues.
View 8 Replies
View Related
Apr 17, 2014
I have a userform with a command button which fires a macro.
everything works fine so far.
my problem is:
I would like to add a msgbox at the end of the macro which confirmes "successfully completed".
I cannnot simply add the msgbox at the end of the macro. don't know what I'm doing wrong.
(see below)
Private Sub CommandButtonOK_Click().
If Me.OptionButton1.Value = True Then
ThisWorkbook.Worksheets("PropertyWorksheet").Range("A1").Value = "Government Securities"
ElseIf Me.OptionButton2.Value = True Then
ThisWorkbook.Worksheets("PropertyWorksheet").Range("A1").Value = "Corporate Bonds"
[Code].....
Unload Me
Application.ScreenUpdating = False
Sheets("MySheet1").Select
Application.ScreenUpdating = False
'run macro
MyMacro1 (adds, hides and deletes various sheets)
MyMacro2
[Code]....
View 2 Replies
View Related
Aug 4, 2009
I am utilizing the following code to force the user of my database to enable macros. This is the
View 4 Replies
View Related
Feb 22, 2010
i want to dis able and enable this button using three criteria, and auto paste the comment to other sheet.
View 2 Replies
View Related
Feb 18, 2010
I have 2 columns of data in an excel sheet and I wish to add this to a userform so that the userform displays the 2 columns beside each other with headings, like a table. The user should then be able to select a particular row and insert it into the specified cell.
I would also like the user to select a row on the table and then be able to bring up another table depending on the row selected...basically so that the user can draw deeper into the information that they require.
View 5 Replies
View Related
Nov 26, 2012
Is there any way for me to put a date in the userform? I created 3 combo boxes (day, month year). I would want it to appear as one date in one single cell in the another sheet as the date the entry was updated in the userform.
View 3 Replies
View Related
Nov 30, 2006
how to enable a button only when a full row has been selected
View 9 Replies
View Related
Aug 4, 2009
The user form consists of 2 pages. The second page can only be accessed from a control button on the first page (much like the next button on a wizard). I have disabled this button. I have 4 check boxes on the page and want the button to be enabled when any one of these is checked.
I managed to figure out the code for enabling the button any one box is checked:
View 3 Replies
View Related
Jun 8, 2012
the code I would need to enable a command button only if any one of 3 checkboxes are ticked?
The command button is cmdenter and the checkboxes are 1, 2 and 3.
View 6 Replies
View Related
Oct 16, 2006
I am trying to enable a command button on opening the Workbook, However it dosent work. I have placed the command button on the worksheet. Programatically it gets disabled on clicking it once. So i want it to be reactivated on reopening the Workbook.
View 5 Replies
View Related
Feb 12, 2014
I have a fairly straightforward UserForm with 4 listboxes and 4 option textboxes (the textboxes do not need to be a part of the validation I'm needing). I then have a command button that I want to be enabled ONLY if ANY of the 4 Listboxes have a selection. I've tried the "Change" event code below and it works upon the first selection of any listbox item.
However, if the user de-selects all selections in the listboxes (i.e. they are all unchecked), the command button remains enabled. I need the validation logic to enable/disable the button to persist as long as the user has the form open and if there is not a selection in ANY of the listboxes. The reason being is that if they click the command button it will update the cells in the worksheet, which if empty may overwrite previous work completed with blank cells.
View 3 Replies
View Related
Mar 13, 2008
I am trying to allow uses of a spreadsheet to be able to select one control button which would in turn disable another one via check boxes however the control buttons do not re-enable when the undo check box is selected.
Sub Lead_REC()
' Set screen behaviour
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
' Defer error handling
On Error Resume Next
Err.Clear
View 3 Replies
View Related
Apr 14, 2013
I'm trying to set up a relatively simple UserForm, which will collect the name of a project from a ListBox, a project update which will simply be text entered into a TextBox, and a estimated completion percentage (factor of 10, 10/20/30% and so on) which will come from another ListBox.
I've managed to get many of the basics working, for example an OK button and a CANCEL button, but there are a few bits that I'm still finding rather tricky.
I have the selected date in the percentage listbox going back into my chosen place in the spreadsheet quite happily, but I can't get it to work for the project name listbox (I think it's because the options come from rowsource and weren't entered manually like the other one). I'll paste my code at the end so you can all see how I've done it (and have a laugh)!
I have also just about managed to get the data to go to the right place in the spreadsheet. I want the data to appear in columns, column 1 for the project name, project update in 2 and percentage complete in 3. I have this working, but it for any reason one cell becomes blank, my code uses that cell, and that could cause entries to become mixed up. I want the UserForm to enter data on the first entirely empty row.
Here's my code:
VB:
Private Sub Cancel_Click()
Unload ProjectUpdate
End Sub
Private Sub Clear_Click()
Call UserForm_Initialize
End Sub
[Code] .....
View 7 Replies
View Related
Jan 13, 2013
This is what i have but its not working.
Code:
Private Sub UserForm_Initialize()
CommandButton4.Enabled = Not (Sheets("0").Range("J8") = "Off")
End Sub
View 3 Replies
View Related
Oct 27, 2006
I have an Excel userform that has 10 textboxes named, say, tbLabel1, tbLabel2, ... tbLabel10. They are all disabled in the beginning. Then I have a question for the user "how many labels you want" and another textbox for the answer. If the user entered, say, 6 as the answer, I want enable the first 6 textboxes, tbLabel1 - tbLabel 6. I tried using
For I=1 To tbHowMany.value
tbLabel & I.Enabled = True
Next I
View 3 Replies
View Related
Aug 17, 2006
I have a command button that I set to be disabled but I want it to become enabled as soon as 3 characters are entered into a textbox.
View 5 Replies
View Related