Restricting Macros
Apr 6, 2009I created a Macro to do some copying and pasting. Can I restrict that Macro so that it can only be used from my PC?
View 6 Replies
ADVERTISEMENT
Restricting Input
Oct 9, 2009I have taken over a workbook that due to various constraints elsewhere I cannot change the structure.
Each of the three columns in question C, E & F have dropdown validation lists.
Column C is either Import or Export
Column E is a list of locations where work is Imported From
Column F is a list of locations where work is Exported To.
Based on whatever choice is made in column C either Import or Export I only want the user to be able to select a value in E or F.
So if Import is selected then a value cannot be entered in F
Restricting Adding Worksheets
Apr 30, 2009We published a 2003 spreadsheet for people to enter expense information, but some get cute and add worksheets to it (instead of sending in multiple spreadsheets). How can prevent a worksheet to be added to our spreadsheet?
View 3 Replies View RelatedRestricting The Search To One Column
May 1, 2007I am trying to get a macro that converts Japanese digits to their Roman counterparts but does it only in a specific column. My original version just selected the J column and then Cells.Replace ran just fine on just that column. I have discovered, however, that the users may make the crucial column either J or K, so I changed it to search for the header first, and then select that column. It replaces in the entire sheet, however.
Here's what I've got (I've only given you one digit replacement. The What doesn't display properly because this forum doesn't display Japanese):
Restricting Cells To Numeric Only
Jan 26, 2007I currently have an input screen where users copy and paste data from a seperate spreadsheet into a range of cells B11:B15 in the input screen.
A macro is assigned to then extract this data and paste into the recording sheets behind.
I would like to format somehow cells B11:B15 so that only numeric entries can be pasted in and not any alpha including #.
Restricting Size Of A Cell
Mar 19, 2007I have a cell in a spreadsheet which has a lot of text entered. The spreadsheet needs to be used by another application (Business Objects).
My problem is that I need to limit the number of characters entered in the cell to 255 characters. I know I can use Data, Validation to limit the text length, but this only works once all the data has been entered, then a message is displayed.
My ideal soulution would be:
When a user is entering text and has reached my 255 character limit, no more text can be entered;
or
When a user is entering text and has reached my 255 character limit, the text automatically continues in the next cell.
A not so elegant solution (but a usable) would be:
A character counter to let the users know when 255 characters have been reached.
Restricting Viewing Of Particular Cells
Oct 23, 2007I do not want to send each rep a file only with their own sales figures as this will be very time consuming. If I could send one file to all representatives but restrict their view to their own figures only, this would make life much easier. If necessary I can set up passwords for them.
Restricting Specific Dates In Any Given Month
Jul 23, 2009This may be a two part question, I finally was able to build a calender contol to insert dates into a active cell, now what i am trying to do is be able to restrict the usage of certain dates. For instance, dates prior to the current date cannot be chosen, nor dates that fall on 29th, 30th and month depending the 31st. Is this possible? Its is being used to calculate amounts for certain days, for example the calender object places a date in Cell A1, which falls between 1st-28th, another column also has a date option, the date to be chosen cannot be before todays date, and cannot be after the 28th of any given month. Is there a way to restrict in the calender or would a if statement apply? OR would a more simple approach to use data validation? where as (A1=MM, B1=DD, C1=YY) so that D1=A1/B1/C1 ie. 07/22/09, so that when using D1 it would be a date format. which would be the start date and E1,F1,G1 for a new date, then use conditional formatting to restrict or prevent choosing dates before TODAY() for the second date colums, I know a message could be displayed for it if it is less then/prior to the current date, or should i have the data validation adjust (if at all possible)for the first set of date options?
View 11 Replies View RelatedRestricting Auto Add Row In Second Sheet To Only One Trigger
Jul 28, 2014I have cut and paste some code provided by members of this great forum to insert a row in a second worksheet at the same row number when one is inserted in the active worksheet. e.g. If I insert a new line at Row 14 in worksheet "admin" I also get a new row at Row 14 in worksheet "report".
Code is as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
Set sourcebook = ThisWorkbook
Set sourcesheet = sourcebook.Worksheets("admin")
Set targetbook = ThisWorkbook
Set targetsheet = targetbook.Worksheets("report")
myRow = ActiveCell.Row
targetsheet.Activate
ActiveSheet.Rows(myRow).EntireRow.Insert
sourcesheet.Activate
End Sub
However, anything I do in worksheet "admin" triggers a new row to be inserted in "report".... If I change text in any cell, or make any changes at all, I get a new row in "report".
Is there a way to restrict this action to only a line insert?
Restricting Moving Onto Another Cell Until One Is Filled In
Apr 17, 2009I'm an intermediate Excel user that can navigate around pretty well, but now my boss wants me manipulate Excel in such a way that I cannot figure out how to do.
He wants the worksheet to be "locked" so that I cannot select another or "the next" cell unless the active cell I'm in has something entered into it. The purpose of this is so that when a manager is buying off (proofing and approving) my work, he can't put his initials in a cell next to the purchased item unless the cell that decribes the purchaed item is filled in. I'm not exactly sure what terminology to use when describing this, so forgive me if I'm being vague.
Can I restrict a user from "moving on" to another cell unless the active cell is filled in? And if so, how do I do this?
Restricting Textbox To Fill Only Negative Values
Oct 29, 2012I want to restrict a textbox to only let the user fill negative values. So first of all he/she shouldn't be able to fill in a text, and only values. And if a value is filled in, then it should be a negative value (automatically).
View 4 Replies View RelatedRestricting Values Of Changing Cells In Solver?
Sep 25, 2011I have 50 share returns for 2009 (A2:A51). I am solving for optimal portfolio by allocating weights to my 50 shares in 2009. in solver, i am changing cells B2:B51, but the criteria is that if any of the 2009 returns is greater than 75 then the weight on that share should be 0. i tried to write an if function in column C of the changing cells but solver overrides the command when i solve.
View 4 Replies View RelatedRestricting Textbox To Fill Only Negative Values
Oct 29, 2012I want to restrict a textbox to only let the user fill negative values. So first of all he/she shouldn't be able to fill in a text, and only values. And if a value is filled in, then it should be a negative value (automatically).
View 1 Replies View RelatedRestricting User To Only Update A Certain Range In A Spreadsheet
Mar 6, 2007I was wondering if there was a way that I can have a user only able to edit the worksheet that pertained to them in a workbook of many sheets. So if there was a sheet called "Alicia" and another one called "Love Child"; user Alicia would only be able to update info on the "Alicia" tab and user Love Child would only be able to update their info. Is this possible? Also can this be done for a particular row in a spreadsheet? So if row 3 has Alicia's info, she's only allowed to update the info on that row.
View 3 Replies View RelatedRestricting Width Of Data Validation Drop Down List?
Jun 5, 2006I have not found a way to control the width of a validation drop down menu. I have 3 drop down list. Two of them are in wide column cells and one a narrow column cell. For some reason my narrow column cell gives me a wide pull down even though the length of the data in this field is only 3 to four characters.
View 2 Replies View RelatedRestricting Vlookup Function For Extracting Data To Single Item?
Mar 16, 2014Currently I am facing a problem that I want to copy data from sheet 2 to sheet 1.Here I am using vlookup function.But I have a problem that when there is two items of same name in sheet 1,it copies data from sheet 2 into both same items in sheet 1.How can I restrict and copy data to only 1 items.The sample data is as shown below :
Sheet 1 (Including duplicate item)
Apple
Apple
Ball
Cat
Cat
In sheet 2 (Unique item)
Apple 25
Ball 36
Cat 34
Restricting Text And Number List To Finite Column Size
Jul 18, 2013I have a long list of data: a unique number identifier that needs to be grouped with text. Right now they are in two separate columns. I would like to have two large columns on a page, as if you were looking through a phone book. I have been trying to get this list to load into a finite number of rows before it breaks into another column on the same page. Then moves to the next page, so about 30 rows that are set 8 rows down on a page to account for company letter head.
I haven't paired the text and numbers together at all. But I haven't been able to find a way to have them move in a way that maintains their correspondence. I have gotten my numbers to move into the row column set I have wanted but not the text.
[LETTERHEAD] {1-7 rows}
1| Shims 31 |Shims 2
. .
. .
. .
30 Bearing Cover 60 |Mechanical Seals {Then it moves to the next page within a certain space. But at this point I would like it to just have this format}
Encourage Enabling Of Macros: Disable Macros When Opening Then The Worksheet Menu Bar And Other Command Bars Are Still Available
Aug 26, 2009I 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.
View 6 Replies View RelatedRestricting An "entered Number" Range?
Nov 5, 2008If I setup a spreadsheet for others to use and I want them to enter their own numbers in certain cells... BUT... restrict those numbers to be from, say 0 to 50... what is the formula or how is it done? In this example I do not want to allow negative numbers or numbers > 50.
View 2 Replies View RelatedEnable/Disable Macros When No Macros Are Present
Jul 28, 2008I 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?
How To Delete Another WorkBook Macros Using Macros
Jul 23, 2005What is the commands or script for deleting a macro automatically using
another workbook macro.
Hiding Macros From The Run Macros List
Jul 14, 2008Is 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.
View 9 Replies View RelatedMacros Really Slow The Another Macros Is Run
Dec 14, 2009I have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k
Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...
For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l
Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.
Version 4 Macros And Vba Macros In Them
Apr 13, 2007how to use excel 4 macros. I do not even know where to put them. I have inherited some spreadsheets that have both Xcel 4 Macros and vba macros in them. (Well, I get a warning that I cannot switch them off when I disable macros on opening a workbook). I cannot even find the offending version 4 macros so that I can re-code them in VBA.
Macros From One PC To Another
Feb 22, 2008how do I install a macro from my laptop to another?
View 9 Replies View RelatedSaving The Macros.?
Apr 23, 2009I'd like to be able to just open the spreadsheet up and have the macros already saved in there. Is there a way to save these macros?
View 2 Replies View RelatedHow To Run Macros Without Excel
Jul 15, 2014I am currently volunteering for a small organisation. I have spent the last week designing a userform to be used by other staff members in their office. However, when I asked the manager 'Do you have Microsoft Office?', I got a very firm and definite 'yes' in response.
It turns out they are using LibreOffice, which is a free alternative. I have also tried to get my userform to work with Open Office - but cannot seem to get either to work.
I have changed the settings in both suites of software to 'Load/Save Basic Code' and enabled macros to run via the security options.
My userform is not that complex. I have limited knowledge of VBA and zero experience using Basic, so have come up stuck.
USe Of Wildcards In Macros
Mar 5, 2007I need to write a macro whereby it queries a cell and if there is any value (it will be either blank or contain characters) in the cell a new row is to be inserted directly above the cell.
Colour Macros
May 11, 2007i have a matrix with all possible values.
i need to develop a macro, which will change the color of the cell according to the value of the cell.
for example if a specific cell in the matrix has a negative value then the macro should change the color of the cell to blue. if its value is positive then the macro should change the color of the respective cell to red.
New To Macros - How Do I Create And Run One
May 18, 2007I have an API that continuously gets stock price. The price appears in one cell at all times - let's call it A1
I want to record the value in a different cell every time it changes, so I can analyze the data.
so A1 = 15
B1 = 15
little later
A1 = 15.2
B2 = 15.2
I did some googling and came to a conclusion that I needed to write a macro.
I see that there is a function or event called Worksheet_Calculate()
Apparently it is supposed to do whatever when a value changes (per calculation)
How do I write a macro, and then execute it?