I'm trying to limit the controls users have on a specific workbook. Problem is I would like for them to have this workbook open throughout the day and if I look all the controls then they will be locked out of these controls on other seperate workbooks they open. The only reason I ask is because of some awesome code I just found by Dave Hawley. The code is...
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With Application
. CommandBars(1).Enabled = Not Sh. Name = "Sheet1"
.CommandBars(" Formatting").Enabled = Not Sh.Name = "Sheet1"
.CommandBars("Standard").Enabled = Not Sh.Name = "Sheet1"
.DisplayFormulaBar = Not Sh.Name = "Sheet1"
End With
ActiveWindow.DisplayHeadings = Not Sh.Name = "Sheet1"
End Sub
This works great with one exception. It will only block the menus for one Sheet.
In my workbook I have 8 sheets and would like to apply this to 4 of them. Is there any way to modify code to hide menus on mores than one sheet. Also while I'm in the neighborhood. Is there a way to hide the sheet tabs for this workbook only. So once again if a user opened another workbook they would be there?
I want a MsgBox to pop-up to the user with 3 Options.
Option A, Option B, Cancel.
If user presses Option A GOTO 1: If user Pressed Option B GOTO 2:
And if Cancel End Sub, not sure if this is doable, please let me know. Basically I am writing this VBA to archive files from the network drive and there are two different kinds and depending on what Option the user chooses, the Destination variable needs to take on a different value, that's why I need this, unless there is a better way to do this.
Basically so far my code is like this:
Sub Archive_GTP()
Dim Nlog As Worksheet Dim DestinationFile, SourceFile Dim lr As Long Dim c As Range
Set Nlog = Worksheets("Log")..........................
I want to write a macro that asks the user for input, but not any input. The input has to be either "D", "IK" or "CI". I would like for the user to have to select one of these options.
I have a table that i use for a customer database. and the end user adds new customers to this table, what i would like is for a message box to pop up whenever cell B2 matches an entry in a column in the customers table. the table starts on row 25, and the column i would like to check for duplicates is column B. I would like the message box to give the user the message "A customer by this name already exists, Would you like to load this customers file?" If the answer is yes, then the row that the match was found on would be copied and pasted onto row 1. if the answer is no, then nothing else happens. I hope this makes sense, i am posting this sheet of my workbook for reference.
I'm trying to write what is supposed to be a simple script that looks at a column of usernames (ColumnA) and passwords (ColumnB). I start off by prompting the user to enter their username in an input box and then I want a loop to check this value against the values in Column A. If the username exists, I want to prompt an input box for the password. If the username does not exist I need some kind of limit on the loop to prompt an input box for the user to enter a new username and password that I'll add to the last row of the data table. The loop part is kicking my butt, this is what I have so far...
Sub Main() Set StartRange = Worksheets("16.10"). Range("A1") username = InputBox("Please enter your username.", "Enter username", "Jdoe") i = 1 Do While StartRange.Offset(i, 0).Value <> "username" i = i + 1 Loop If StartRange.Offset(i, 0).Value = "username" Then password = InputBox("Please enter your password.", "Enter password", "password") End If End Sub
Any way to limit the date a person can input to the fourth business day of the following month. So limiting the date the 06/05/2014 for instance. Would there be a way to keep this updating so manual updating would be unnecessary?
Is there any way to select things like tools - options across all worksheets in a workbook? I often want to suppress display of zeroes in an entire workbook, but so far the only way I've found to do this is to go to eash worksheet in turn and go thru the procedure on each one.
I have some code which enables new comments to be added when a user double clicks a cell, but I want to restrict this to a specific range B5:B125. How do I change the code to reflect this, and add the current Date to new comments added. Here is the first section of the code which sets the range etc...
Option Explicit Public oldRange As Range Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) On Error Resume Next Dim rng As Range Set rng = Target(1, 1) oldRange.Comment.Visible = False With rng If Not .Comment Is Nothing Then If .Comment.Visible = False Then .Comment.Visible = True Else .Comment.Visible = False End If End If End With Set oldRange = Target(1, 1) End Sub....
I have a save button running a macro that validates before saving. Is there a way to remove all other save options (alt f a, ctrl s, the toolbar save button, etc.) but just from this one workbook?
A while ago I tried playing with a code that did this but it did it through out excel.
I have an Excel 2007 Workbook that has a variety of different reports. I’m trying to find a way that will allow me to reset reports back to original setting after the report has been completed. Sometimes a report may take a couple days to complete, so closing and saving is a must. Any thoughts how I may achieve this.
Is there a way to fix the the allowed numberformats in a workbook?
For example, is it possible to have only the following numberformats in a file : 0.0, 0.0% and $0.0. The file should not allow any other formats, and the user should not be able to create custom formats.
Is it possible to have a message box show only for a specific user when a spreadsheet is opened? I have a spreadsheet that multiple people use, but only a few edit. I only want the message box to show for those that edit the form.
I have a relatively simple, though large, spreadsheet. I get the following in many of my cells: The Error Checking Options button is displayed and the cell itself. is marked with a small green triangle in the upper-left corner. When I click the button, the error type is displayed, followed by the following list of error-checking options:
information specific to the error type
• Show Calculation Steps: demonstrates all steps leading to the error • Ignore Error: allows you to accept the formula as entered, without Excel displaying the Error Checking Options smart tag • Edit in Formula Bar: allows you to edit the formula that is generating the error in the Formula Bar • Error Checking Options: opens the Options dialog box, where you can select the rules governing error checking • Show Formula Auditing Toolbar: displays the Formula Auditing toolbar
When I check the formula it calculates correctly. What triggers this when there is no error, is there a way to prevent it from appearing in the worksheet?
I have a spreadsheet of sales figures that I want to interrogate to show data relating to sales of specific manufacturers products to specific customers, I have a colomn (call it A) that has the customer name listed, coloum B that has the Manufacturer name, coloum C that has the type of product it is and coloum D, coloum E, coloum F, coloum G that has the sales figure for the last 4 quarters.
I would like a user input box to pop up on loading, which asks for the Manufacturer I want to search for, and then a report genertaed that lists each customer with their sales figures by type of product for the specified manufacturer for the last 4 quarters (individually not tottaled). If the customer doesnt have any sales figures for that Manufacturer I would like that listed showing '0'
I have data in 2 different workbooks, and I need to transfer all data in 'Jx_ex1' to sheet1 in the 'template' workbook, and all data in 'Gr_ex1' to sheet2 in 'template' workbook. All data should be transferred to the same cell numbers, from A1 to Y5000. I actually have loads of workbooks to transfer to the template but if I have an example with the two then im sure I can work out how to modify it. All files will be in the same folder.
I have nearly finished my project, but need to make the last real bit of code, then just some general tidying up.
What I want to do - I currently have two tabs in a userform - one called 'main' and one called 'extra'. - I want Main to always be shown to the user. - I want Extra to be hidden (is it possible to make the tabs invisible?) and for this form to be shown only under a specific condition
I have a question on my userform (using Option Buttons): "Did the customer ask about an extra product today?"
If the answer to this question is no - When the user hits the command button (after filling out the rest of the form), do nothing special - just return all the values to the worksheet
If the answer is yes - When the user hits the command button, I want them to be taken to the 'extra' tab. They will fill out some additional check-boxes, then hit another command box, which will return the info from both the 'main' and 'extra' sheets into the same row of the worksheet.
In brief: I want my 'extra' tab to be hidden, only to appear when 'yes' is selected, and for the results all to go back to the worksheet.
My worksheet with macros gets messed up when a user selects either multiple cells from column F or selects multiple cells in any one row (but only if column F is included) in the range C17:O116 and hits "delete". I will either do an exit sub, an application.redo, or something similar so that this doesn't happen, but I don't know how to write the code to trigger these events. So I need a way to determine "if the user selects multiple cells in column F and hits delete" or "if a user selects multiple cells in any row from 17 to 116 that includes the cell in row F, and hits "delete.""
Set Rng = Range("F17:F116") Set rng2 = Range("K17:K116") Set Rng3 = Range("L17:L116")
I need to import some spreadsheets that are sent to me into a master database spreadsheet. I have attached both below. The complaintstest.xls is the master database and the other is the sheet that will be imported. What I would like to happen is when the macro is run, the user will be able to select the sheet they would like to import through a dialog box. After the sheet is selected the macro will import the cells from the selected sheet into the master database in a particular order. In the master database example on row 2 I have listed the columns from the sheet to be imported in the corresponding column on the database sheet. This will macro will be run a couple of times a week so the macro will have to find the next empty row to start the import on.
I'm wondering if someone can help me with this. I am using the excel calendar control and trying to activate it when a user selects a specific cell "C3". I can get it to activate when I move off the cell, but it's not working when they click on the cell.
Sheet1 Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C3")) Then Call OpenCalendar MsgBox "Calendar" End If End Sub
Userform2 (I have this code under here)...............................
I need to copy and paste the specific dates from the specific city that the user will insert in the "dash" sheet. Basically, I need the macro to read the city and range of dates that the user will type into the "dash" sheet and copy the columns "AA" to "AD" from rows specified by the dates input. Once it has been copied, it has to be special pasted just the value onto the sheet "1" beginning at location B2.
the the data will not be available as it is vlookups to another excel sheet that i have not provided. I have deleted the data, but you can pretty much put 1's everywhere.
All, can I protect my worksheet while at the same time allow users to unhide a specific range of cells?
I'm hiding cell range FA:FK, but based on certain conditions I want to allow the user to unhide the cells but I do not want the user to be able to unhide any other hidden ranges. if there is a way to do this without using VBA. If it cannot be done with VBA
I have created a user form using the addins that come with excel to create a calendar that will allow the user to select a date and automatically put that in a specific cell. My system is office 2000.
The code is as follows:
Private Sub OK_Click()
Dim i As Integer Dim myCell As Range
i = 0
For Each myCell In Selection myCell.Value = Calendar1.Value + i i = i + 1 Next myCell
Unload Me End Sub
Now, I gave it to my boss who has xp, and I get the following VB error when she chooses a date and clicks on the ok button:
I have a workbook that is used to calculate values from various data etc. On completion of calculating the values, eg. £20,000, I want to put the values into specific cells of another workbook template. I can of course do it manually by selecting the cell of the template workbook and do an = then reference the the cell in the calculating workbook.
What I want to happen when all the calculations are complete is that:
1. A new workbook, based on the template, is opened and saved with an appropriate title
2 The values from the calculating workbook are automatically entered into the new (saved) workbook at specific cells
Trying to determine who has a file open, the code below works for simple files. However if the workbook contains macros and user forms it seems there are multiple occurances of the search terms strFlag1, strFlag2. How can I determine the last user
Function LastUser(path As String) Dim text As String Dim strFlag1 As String, strflag2 As String Dim i As Integer, j As Integer strFlag1 = Chr(0) & Chr(0) strflag2 = Chr(32) & Chr(32) Open path For Binary As #1 text = Space(LOF(1)) Get 1, , text Close #1 j = InStr(1, text, strflag2) i = InStrRev(text, strFlag1, j) + Len(strFlag1) LastUser = Mid(text, i, j - i) End Function
