Right now, I've this pop up box which prompts users for passwords and user name for log in. They are two main users : Admin and User. Admin is the user which is allowed to see all sheets. While User is only restricted to user interface sheets.
The problem is for the "User" I want to restrict a view in the sheet
"ELEMENT". I wish to hide columns L to AI if "User" logs in. But if "Admin" logs in, I want no columns hidden. Everything must be visible.
Private Sub CommandButton1_Click()
Sheets("Main").Select
Dim strUser As String, strPword As String, strWs As String
Dim w As Worksheet, c As Range, r As Range
strUser = Me.TextBox1.Value
strPword = Me.TextBox2.Value
Select Case strUser
Case "User"
If strPword = "User" Then
Sheets("SMXINVENTORY").Visible = xlSheetVisible
Sheets("SMVINVENTORY").Visible = xlSheetVisible
Sheets("SMIINVENTORY").Visible = xlSheetVisible
Sheets("SMF1INVENTORY").Visible = xlSheetVisible..............
I have a column that is updated several times a day, I need to be able to view that list in a userform combo box and that box to be able to react like an autofilter list. I've tried the advanced filter thing but as th elist is contantly updated it makes this very hard to handle as I have to set macros to compile the list and then delete it once it's been used. Is there a way I can set vba code so the ComboBox populates unique fields, sorted AZ and removed the blanks without using the advanced filter..?
Is there a way to have 2 views of the same sheet that will enable either user to only see their own requirements (columns, row)s but that will update the overall worksheet. The users would not be accessing simultaneously. Complex sheet so just need to have user access to a subset of data.
I have a very complex data source (80 columns, thousands of rows) and need different types of Pivot tables for analysis. I have created around 20 Pivot tables in different sheets based on the same data. What I wanted to know was, is there something like a saved 'view' which I can select in the Pivot table to generate different pivots in the same sheet (with different settings of rows, columns, etc but based on the same data)? Currently, to view the other Pivot, I have to go over to the other sheet - or customise the current pivot.
I'm dealing with a spreadsheet that uses custom views. Before I do anything with it, I want to make sure that there is no custom view enabled (e.g. just display all). I've tried Chip Pearson's ThisWorkbook.CustomViews("View1").Show suggestion, but it just throws "Runtime Error 5: Invalid procedure call or argument". My actual code is: ThisWorkbook.CustomViews("All (Est)").Show
I have a worksheet set up with a number of pivot tables to show job summaries for each project manager. I created buttons labeled with each project manager that trigger a macro to filter the results to only show jobs for that specific project manager. There's one button for each project manager.
I'd like to create a macro that will essentially show each project manager's report and compile the result into one PDF. I only have 5 project managers so I don't need an array or anything if that makes it easier, I could write the code manually for each one.
So basically, it would do this:
1. Run macro to filter results to only show Project Manager #1 jobs. 2. "temporarily" print to PDF. 3. Run macro to filter results to only show Project Manager #2 jobs. 4. "temporarily" print to PDF. 5. Run Macro for Project Manager #3, etc..... ............... Final step. Compile all the "temporary" pdfs into one file.
I have created a report with 22 sheets as different pages to be included or hidden depending upon the type of report needed. I had found several sources saying to use the Custom Views function. So for example I will unhide all the sheets and add as a Custom View "All Sheets" and then whittle it down to the 5 I might need for B type inspections and add that view as B Inspection. But when I try and toggle between them I'm getting an error message that says "Some view settings could not be applied" - and thew saved view I was looking for is not the same sheetwise.
1. I have a shared and protected workboook that about 15 people update. For some reason, when people open it they can't always scroll. Anyone know why this happens and how to fix?
2. Is there a setting so that whoever opens the workbook will see the same view (instead of whatever view the last person saved the file in)?
I am trying to use a checkbox to control the custom views that i have set ("Normal", "Hide") However after i entered it in VBA, it just does not work. After i check or uncheck the checkbox, it just keep going back to the "Normal" view.
Sub CheckBox1_Click() If CheckBox1 = True Then ActiveWorkbook.CustomViews("Hide").Show Else ActiveWorkbook.CustomViews("Normal").Show End If
I've posted this on Experts Exchange too, specifically trying to target the SQL Server experts. http://www.experts-exchange.com/Soft..._24423637.html
SQL Server isn't really my thing. I have been asked to quote on a job to build some models. One of the components is to pass parameters to sql server views and return the data into a table in Excel. I never done this.
1. Is it possible to pass parametrs to a view thru Excel? 2. Will the view execute based on the users permissions (different users using the Excel template have different access rights)? 3. Any other things to consider?
I don't need specific solution, only really some broad advice. I really only want to know how feasible this is...
If vntFile <> False Then ActiveSheet. Cells(27, 1).Value = vntFile Image1.Picture = LoadPicture(vntFile) Image1.PictureSizeMode = fmPictureSizeModeStretch
End If End Sub
I would like to force the box to open in Views - Preview instead of List or Details or whatever it is at. I tried the following (and a bunch of other things), but I could not get it to work
With FileDialog .InitialView = msoFileDialogViewPreview End With
I have a large spreadsheet that I am managing and there are many users who access this for information. Because of this, we have all created "custom views" for our departments.
I continue to struggle with how to save changes in these custom views. Yesterday, I added and moved some columns to the spreadsheet and now all the custom views are messed up. The only way we have been able to work through this is deleting and creating new views.
I have a spreadsheet with four columns of data for every month (i.e. January has a Prior Year, Budget, Outlook, and Actual column). I have been trying to come up with a macro or form that will allow me to customize which columns I would like to see and hide the rest. For example, if I chose to see the Budget and Actual columns, it would hide the Prior Year and Outlook columns for each month.
For an old project which was a till system i had two sheets,a data sheet and till system sheet , the data sheet contained :
example: column A - numbering of the data (to be used with V Lookup) column B - product name Column C - price
This would then be replicated in the next 3 cells for the next data category.
Till system then had a combo box which had a cell link on the current page and data from the data sheet and then i had a price column next to i (containing V Lookup formula) the price then changed depending on the choice in the combo box.
I want to incorporate combo box's in to this new project. If i can then get some kind of stock thing i intend on then using conditional formating to colour code stock levels to show severity of needing to order etc..
It is telling me there are too many levels. I am not an Excel expert, so I am trying to figure out an alternative to this command, I am sure one is available.
I have a report of employees' hours reported for the week, which I process payroll from.
I have sorted the spreadsheet first by employee number, and then by date. I have subtotaled each day's hours (with a formula rounding each day's hours to the nearest quarter hour).
I now need to add a subtotal of weekly hours, per employee number, and I cannot figure out how to do this.
Here is an example of what I want to do. I have highlighted the second subtotal I want to add to the spreadsheet, but have been doing the =sum(xxx) formula for each employee because I can't figure out how to subtotal again.
David L 9/30/2013 0005 360 6:00 A.M. 12:00 P.M. David L 9/30/2013 0005 112 12:30 P.M. 2:22 P.M. David L 9/30/2013 0005 115 2:22 P.M. 4:17 P.M. David L 9/30/2013 0005 13 4:17 P.M. 4:30 P.M.
I have daily data that has columns for day, day of week, week end, month, year. I am trying to create a pivot chart that displays the data as a line chart day by day and as a secondary axis column chart by week.
How can I format a Pivot Table with 5 levels. I try to use the "Design" but it only format the first 2 levels of the Pivot Table. I need to distinguish each level with different colors. I think manually is a bit difficult.
I need to convert levels to numerical values and then: Firstly, add together two vlookup values THEN divide by 2 to get an average AND THEN see if this average AND a second, individual lookup value are above a specified another value, which may be different. IF all these criteria are set, return, "yes" if either the first or second, or both criteria are not met then "no"
Or put it another way. if lookup values A+B/2>"5" AND C>"3" then "yes", Else "no"
I created a PivotTable that works fine. A user can click on an option and the subcategories expand; then he can choose one of those and a new group opens, etc., going 4 or 5 levels deep in options.
What I need to do is: If he then wants to start over and choose another option in the initial level, I want all of the subcategories to reset to their original closed state (unexpanded). As it is now, when he goes back to the original choice, that is closed, but everything inside it is still open as he had selected them previously.
How do I enter a formula to find a dollar amount for a different range dollars? For example, if $0 - $1000 = $50 and $1001 -$2000 = $100. In reality, I would like to enter a specific dollar amount within one of the levels (column 1) and the sum (column 2) would auto fill.
I have a 14 step Lead Generating process and I want to color code each step as a level of how hot it is by where it is in the process but I cannot get 14 different reds.
When setting it up I went to More Colors, then Custom and changed the Color model to HSL and all I got was White with White borders.
I have tried to pick 14 reds from the standard colors but the difference is not very noticeable.
I have a maximization problem. I have to maximize Kitchen Sets given available materials. I have 11 different Kitchen Sets and every set requires different material.I have attached an excel spreadsheet for making the problem more clear.
I have T1;T2;T3;T4;M1;M2;M3;M4;O1;O2;O3;O4;Z1;Z2;Z3;Z4 different materials. Every kitchen sets requires one material from T which is fixed (for example T2), one material from M which is fixed again (for example M3), one material from O which is fixed (for example O1) and one material from Z, which is fixed (for example Z4).To complete a set the sum of all materials used should equal to 4. I have some constrains T1+T2+T3+T4<=2 it is the same for M1+M2+M3+M4<=2, O1+O2+O3+O4<=2 and Z1+Z2+Z3+Z4<=2.
So basically my goal is to say which materials should I keep in stock in order to maximize the Kitchen Sets.
On opening a workbook (XL2003) I want users to enter a password which will give them read Only permissions or access to the whole book. I do not want use the "save as" option it is not suitable for my needs & here is why. I have about 10 staff who need to fill in timesheets using XL SS on a public folder on the server. The staff need full access to file & management such as myself only need to view (read only) the timesheet without the ability to change data. Using the "save as" function the staff need to input 2 passwords.
The staff (in general) are not overly computer literate so I was hoping to be able to write code so that on Workbook_Open event only one password is entered and depending on the password gives the user full access or readonly access. This will also save management having to remember different passwords for read only access to different staff timesheet files & will give crude protection to the files.
Private Sub Workbook_Open() Dim Message, Title, Default, Password As String Message = "Enter your password" ' Set prompt. Title = "Password" ' Set title. Default = " " ' Set default. ' Display message, title, and default value. Password = InputBox(Message, Title, Default) If Password = "test" Then Workbook.ReadOnly = True Else Workbook.ReadOnly = False End If
End Sub
Error occur on "Workbook.ReadOnly" lines . I also want to put an errorchecking code for invalid passwords. Again, I know I can use the "save as" option but that is not what I need.
to create an order ID so when customer is transferred from the new order to Existing orders it will automatically generate CUW001 for first order then CUW002 for the second order and so on I have added a column form Order ID so this may have affected the macros pasting location. Also one last thing could you also tell me how I could highlight stock items red if the stock levels fall below 5
I have been using a spreadsheet which will allow you to hide columns and rows with a button associated with that row or column. I need to adapt this spreadsheet for another purpose but dont know how to recreate this feature. I have attached a screan capture to assist with my description