I 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.
I 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 #.
I 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 RelatedI have a text box. I want it to pull the data from Cell B2 and list it in the text box. The problem is that I want to be able to change the data if the user clieck inside the box and changes it.
View 3 Replies View RelatedI 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 View RelatedI 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
We 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 RelatedI 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):
I 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.
This 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 RelatedI 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?
I'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?
I 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 RelatedI 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 RelatedI 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 RelatedI 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 RelatedCurrently 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
I 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}
Is there a way so that on his computer he can tell Excel that each sheet to be viewed should have a standard/automatic view set to 100% or another percentage?
View 3 Replies View RelatedI've done the following steps so far:
Tools
VBAFormatProperties
Checked the "Lock Project from Viewing"
Applied a password
and clicked Ok
I have a workbook with several worksheets. I need to prevent users from viewing worksheet mgrview unless they have a password for it.
View 1 Replies View Relatedis it possible that when i open a .csv file in excel i can make it only display some of the columns? The program i am using exports more info then i need and it gets very tiresome to delete each column manually.
View 9 Replies View RelatedI am not sure why but when I opened a particular file, the file hides somewhere in Excel. I am unable to view the file but when I try to open the same file again, Excel prompts that the file is already open but I can't see the file.This is an important file at work and I am not sure how to actually get to view the file. I tried copy and pasting the file in another location and then trying to open in Excel. However, the same thing happened.There is no error message.The file is opened but I am unable to view, as it seems to hide from view.Is there a solution?
View 7 Replies View RelatedI have a semi-large macro which I have been creating on and off for a few months now. The file will fundementally be used by people with little knowledge of Excel, hence I force the user down certain alleyways depending on what they have previously done with the aid of veryhidden sheets and such like.
Upon opening the file, some code is ran to hide all of the sheets in the file apart from the home page. This is a pain when I am still working on the code though, so a portion of it is commented out. When I uncomment the "on error.. - next sheetIn" so that the file runs as it would be used, the code itself works perfectly and hides all of the sheets apart from the home page. Unfortunately, I can't then view the VBA code as it instantly crashes Excel (and any other Excel file I currently have open). This happens whether I click the Visual Basic button in the Excel ribbon, whether I try and access it through the design mode on a control, pressing Alt+F11 or even opening up another spreadsheet containing code and attempting to click on the broken macro after viewing the VBA code for the working spreadsheet.
VB:
Sub workbook_open()
'stop screen flickering whilst running the code
Application.ScreenUpdating = False
[Code]....
The only way currently to get back into the code is to open the spreadsheet up with macros disabled and recomment the code, but obviously this loses the function of the code.
I have large workbook with various sheets. In my first worksheet I have some command buttons that call each sheet as needed. However, I would like each sheet to be displayed either as a web page or in a way that the user does not see all of the menus, bars and so on.
View 1 Replies View RelatedI'm trying to open a .xlsm on an Android tablet with only OfficeSuite installed. Initially, that didn't work. After installing a DocumentsToGo application, it shows me my spreadsheet, but none of the .jpg's that are visible in the original file. I copied all .jpg's to the tablet; both the .xlsm and the folder with the pictures are in the same parent folder, to simulate the original folder structure in Windows. Still the pictures will not show.
View 1 Replies View RelatedI have a workbook containing a number of spreadsheets. Some of the spreadsheets are user inputs. The results of the user inputs drive a number of final reports. The final reports (i.e. spreadsheets) are hidden from the user (I don't want the user to be overwhelmed with so many tabs when they open the excel spreadsheet).
I created on the main input tab spreadsheet the following:
1. Check boxes - so that user can select after making his/her inputs the reports that he/she wants to view or print.
Say there are 4 reports (call them Sheet1, Sheet2, Sheet3, Sheet4 - therefore, 4 check boxes. Through the Format Control, the checkboxes have cell links that yield TRUE (if selected) or FALSE if not selected - linked to cells A1, A2, A3, A4 respectively.
2. Option buttons - one for view and another one for print. Through the Format Control, the View and Print option buttons have cell links to cell A5 yielding 1 for View and 2 for Print.
3. Command button - that will clear the check boxes
Issue
I would like to know if there is a way to code in VBA to:
1. Unhide the spreadsheets corresponding to the check boxes if selected;
2. Print the spreadsheets corresponding to the check boxes if selected for printing; and
3. Clear the checked boxes to unchecked if the Command button is clicked.
I'm struggling with coding to perform the above tasks.
I have a excel file with around 8 sheets in it and this file puted in the network to be shared with 8 persons So i need to make an individual password for each one to enter to his specific sheet and have full authorization to make anything in it and this done using userform where each one when open the file in the network , a user form open in first stage to select his name and put his own password with the ability to let each one to make his own password or change it from the userform
this file is shared so it may more one user open it and it may be there an save funtion at same time ( same time & same activities )
I am looking for the code to keep certain cells from being rested on, they can be clicked on but in order for you to view the formula in the cell you have to hold down the mouse button otherwise it will move you over to the cell next to it. I was using another worksheet a while ago and was trying to view the formulas but when I clicked on the cells holding them it would move me to the next cell, the only way I could view them was to hold the mouse button down as soon as I would release it it would be gone! I do not want to protect the sheet, if there is another way to hide formulas I am open to all ideas!
View 4 Replies View RelatedHere is what I try to achieve:
- I have a "Menu" worksheet with basic Hyperlink to Sheet1, Sheet2 etc.
- When I click on a link (or on the tab), I want a prompt asking for a password (different for each tab). I do not anyone who doesn't have the correct password to see the content of the worksheet.
(I don't know VBA and I couldn't modify the code to fit my needs)
Here is the code posted
Dim sLast As ObjectPrivate Sub Workbook_Open() 'Ensure Sheet1 is not the active sheet upon opening. If Sheet1. Name = ActiveSheet.Name Then Sheet2.SelectEnd