User Friendly Multiple Sheet Navigator
Mar 8, 2008
I ‘m trying to put a drop down combo box on each sheet (around the top right) of a workbook (20+ sheets). The drop down list will have each sheet in the workbook. Selecting the sheet name in the list will make that sheet the active sheet. I plan to remove the tabs from the window, because only about a third of the tabs display at once, and right clicking to the right of the tabs only displays about 2/3 of the sheets.
When I was searching this topic, I found a post that used AllWorkSheets function to create an array of worksheet names and the procedure adds it to the data validation in cell "A1". I don’t have the same cell available on all the sheets, at least not at the top of the page, so I thought a combo box that can be moved would be a good solution, but I’m open to any suggestions that allows the user to navigate the sheets w/o a macro always running or a form always open.
View 2 Replies
ADVERTISEMENT
Nov 21, 2009
Want to go to a particular sheet in the same workbook by selecting the sheet name from a list in sheet 2 and clicking a command button. Sheet 2 has a list of all sheets in the workbook and sheet names are in two cells in column A & B (e.g. tdm-216). User to select two cells which are the desired sheet name (all sheet names are unique) and then click macro to jump to that sheet in the workbook. There will be over 100 sheets in the workbook.
View 6 Replies
View Related
Oct 14, 2008
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
View 2 Replies
View Related
Jul 14, 2009
I am writing a macro in which the user selects several ranges (entire columns) for the macro to manipulate. I display a userform to the user that contains 16 refedit objects (1 for each range that they picked). Unfortunately, the value that appears in the refedit boxes is not very user-readable. It looks like this:
'[WorkBookName.XLS]Sheet1'!$B$5
I can assume that all of the ranges will reside in the same workbook, so I have been cutting the workbook name portion off as a string and displaying it in 1 place only. I can leave the value of the refedit as: Sheet1'!$B:$B, but even that is not very friendly. Of course if I do change the value of the refedit controls then I will need to "fix" them before attempting to access the range.
Does anyone have a suggestion as to how to display the sheet and column of the range to the user?
View 6 Replies
View Related
Jun 5, 2009
attached is a sample schedule, there is conditional formatting in all of the cells (see graphic) such that if the cells to the left are the same value, then the cell is turned white, and a border is removed.
This gives the appearance of a 'block of time' while which cleans up the appearance of the spreadsheet.
the problem i have is that when we scroll to the RIGHT, we lose track of the 'left most' cell...which is black of course, the similar ones to the right are intentionally whitened out.
a perceived solution would be to change the conditional formatting of the column (actually only the cells in the column we care about) when it is NEXT to the vertical freeze pane so that it appears not in white, but in black font, etc.
the TRICKY part is that we will obviously need to go back and forth, so the columns will need to change formatting often.
or
when the column number (or letter) next to the freeze pane changes, change the formatting to certain cells in that column to an alternate version; and change the formatting of all other certain cells within other columns to original formatting[
View 9 Replies
View Related
Sep 5, 2013
I have a file that contains detailed sales information for each sales person. It typically contains over 20k records and the managers find it difficult to look up their sales people.
Currently it is subtotaled for each sales person but there are about 450 sales people across the country and it's not easy to use.
I have seen other Excel files that use buttons and I am wondering if this might be the solution to this issue. In other words, can I create a button where the managers can just click it, key in their sales person's name and get at the records easier? If so, how does that work? How I could make this file more user friendly.
View 9 Replies
View Related
Apr 26, 2013
Is there any way that multiple user are using different excel based user form and update data using that. Is there a way that all the user save the data directly on the main database?
View 6 Replies
View Related
Jun 16, 2006
find attached zip file . if you open the file then you can understand the problem.
I need to change cells color and text color as per user choice in Password protected sheet : (user is useing excl2000)
Kindly open attahced file i putted the note inside the file.
View 3 Replies
View Related
Jun 21, 2006
i have 2 workbooks open, i need to write the code so that i can copy the sheets from each of the workbooks to a whole new workbook. this does not seem like a difficult task at all. so here are my problems:
1) once im in the activewindow, how can i copy the sheet it has in it, since i do not know what the sheet is called, and also i should store the sheet name in a variable so i can reference it later
2) how can i switch to the other window without knowing what its filename is
View 3 Replies
View Related
Dec 24, 2013
I want to do a loop where you can copy say A3 worksheet 1 then add another sheet naming the work sheet "A3" then copying A3 worksheet 1 to A1 "A3". After that looping to A4 to a new work sheet naming the work sheet "A4"copying the value to A1 "A4", etc...
Is there a simply way of doing this loop? I can probably fit my other coding into the structure.
View 4 Replies
View Related
Mar 22, 2014
I have a challenge in excel functions, I need to save a file as common folder but in that workbook have multiple sheets need to show the sheets as per the user name on system other sheets want to hide with password protect,
example,
file name:- Report
Sheets:- A,B,C,D
if now using system user name is A then need to show only "A" sheet, other sheets want to be hide and the same for all.....
View 2 Replies
View Related
Oct 10, 2013
I have a workbook with several sheets. I have written a macro to check if the value in a column is matching with that in another column in another sheet and return the value in a preceding column in the second sheet into a preceeding column in the first sheet. The code looks likes this and seems to be working fine.
Sub Copy_Price_Code()
Dim Rw As Long
For Rw = 6 To Sheet6.UsedRange.Rows.Count
[Code] ....
However, there would be more sheets in the workbook in future and I need to edit the macro in such a way that the user can select the source and destination sheets which I could use in the above macro. I tried to do it as follows but its not working.
Sub Copy_Price_Code()
Dim Rw As Long
Dim Rw2 As Long
Dim Sourcesheet As String
Dim Destinationsheet As String
Sourcesheet = InputBox(Prompt:="Enter source sheet name. (example: Sheet1).", _
[Code] .......
View 3 Replies
View Related
May 14, 2007
I have an Excel sheet that displays 50 rows and 20 columns when it is opened the first time. These rows and columns are the only ones that I want the user to be able to see. The user shouldn't be able to navigate the sheet by using the scroll bars or the wheel on the mouse. I have of course already removed the scroll bars but I don't know how to handle the scroll wheel problem.
View 2 Replies
View Related
Nov 4, 2012
We need to have various users input data into a spreadsheet, so I have created a userform. Just need some code with the submit button to populate the central sheet, I think that I will need it to open the sheet, paste the data, save and close it.
This is my submit button as it stands, with the form and spreadsheet in the same workbook:
Private Sub btnSubmit_Click()
Dim RowCount As Long
Dim ctl As Control
RowCount = Worksheets("Sheet1").Range("B1").CurrentRegion.Rows.Count
With Worksheets("Sheet1").Range("B1")
.Offset(RowCount, 0).Value = Me.coboPropSalutation.Value
[code]....
View 1 Replies
View Related
Jan 12, 2009
I need a macro that will create a popup message any time 1) a new worksheet is created and 2) a worksheet is copied. The contents of the message left aside (use "message" as our example message)
View 9 Replies
View Related
Mar 6, 2007
i want to lock the excel sheet..so user cant add any data or delete it...only through VBA form????
View 9 Replies
View Related
Jun 12, 2008
I recently created a userform. I want the data to be placed into the leftheader, centerheader, and rightheader of the document after the user fills in the required fields. I have made the userform, and I can make the code for it to place one item on my userform in the left, center, or right headers. However, when I try to place more than one item in any of the headers excel deletes the first item and overwrites it with the item that is listed last in the VBA code for that respective header. I am new to VBA (this is actually my first program) so I do not know how to make the first value stay there and simply have the next value placed on the line below it.
View 13 Replies
View Related
Feb 9, 2010
The User has made multiple selections with the mouse. The spreadsheet is filtered. The user will usually make different multiple selections on the following columns: A and X through to AR (inclusive).
I just need some code to capture these various multiple selected ranges so that I can copy the selected range as shown below:
View 3 Replies
View Related
Nov 4, 2006
how to create user logins and passwords for five users for one workbook. Also, preferrably to have a message to prompt for entering the user name and password at the first worksheet (e.g. the main page where there it is usually the company logo page) when the workbook is open.
E.g. there are five employees and we would like to create five different logins and passwords for them. So when they open the workbook, it would be the first sheet of the workbook to appear first and they would then be prompt to enter their username and password. If one of the employees leave the company, we can still quite easily change the name to the new employee.
View 6 Replies
View Related
Mar 19, 2007
In VBA i have 3 userforms, the first 2 take the data and store them in variables. The 3rd form is supposed to take the variables from the other two forms and do some calculations with them. Although i cant get the 3rd form to take the stored variables from the other two forms. Is there a way to use stored values from other userforms?
View 3 Replies
View Related
Jun 28, 2007
Basically at the moment our static data group use a generic sheet which lists all possible field variables. However, what I want to do is design something which only shows the fields that need to be filled out (ie if the field is shown it needs to be input).
Once the form is filled out it will get saved and sent via email, where it is then printed out and input by our data group. One way I thought about approaching this was to have 18 sheets which are hidden, and which are then selected when a report type is filled out. The other way would be to have IF statements but I think that would get complicated on the one sheet. Just really looking at ideas at this stage.... For example there are 30 fields on the sheet that we use now, but some reports may only need 5 of these fields - I therefore need to "hide" the remaining 25...
View 6 Replies
View Related
Jul 28, 2007
I'm creating an addin that will go on a shared server and be used by a number of different users. How do I go about being able to store user settings in a sheet on the addin if multiple users have different settings? Do I need to somehow make the addin read only?
View 7 Replies
View Related
Mar 26, 2008
I have a control button that uses this code
ChDir "H:EMCARCHIVE"
varFileName = Application. GetOpenFilename("Excel Files (*.csv), *.csv, (*.xls), *.xls")
however the files list but when you select any one of them they do not open and the file name is not displayed in the filename box
can you let me know why the files do not open
View 4 Replies
View Related
Jul 4, 2014
I have a database file where users name can be located in 2 neighbor columns. Against each user name (in next 2 cells on the right) there is a evaluation (good or bad).That database will be growing and new additions will be added each month. That means one user can appear several times on a different row with different evaluation.
In the same file I have a separate sheet where I need after running a macros to have aggregated list of users (in one column) and against each user I would like to see how many how many good and bad score he have. It will be good if users are sorted in alphabetical order.
I am providing an attachment with example file.
View 2 Replies
View Related
Mar 29, 2012
Here is an excerpt of my code
Code:
Else
Sheet8.Unprotect Password:="rm123abc"
Sheet8.Range("V" & rw) = "Item is not found in the Data Base. Please Contact DME for all documentation requirements for this item"
Sheet8.Protect Password:="rm123abc", AllowFiltering:=True, AllowInsertingRows:=True, AllowDeletingRows:=True
End If
Unfortunately, once the code has run and has finished re-protecting my sheet, it doesn't allow the user to select locked cells. These locked cells contain hyperlinks, and they need to click on these hyperlinks to navigate through my workbook.
So I have two questions:
1. How can I enable allow user to select locked cells
2. Do I have to allow the user to select locked cells to access my hyperlinks, or is there another option?
View 2 Replies
View Related
Jun 19, 2013
I have got a protected sheet with macros, how can I ensure that users can only open the sheet as Macro enabled only otherwise the sheet would not open?
I understand that some users may have different Macro security settings?
View 1 Replies
View Related
Jan 22, 2010
I'm working with a workbook that has about 75 sheets. I need a macro that will copy the info in a certain couple of ranges from one sheet to the same ranges in another. This would not be a problem if it was always the same two sheets. But I need the user to be able to specify which sheet he wants to copy from and which sheet he wants to copy to.
The ranges on the sheets will always remain the same, just the sheet name needs to change. I created drop down boxes for the user to choose the copy from and copy to sheets, but I don't know how to insert that into the VBA code. The way I've designed it, the macro needs to read the result from the user that is on sheet "Index" in cell H5 to copy from and N5 to copy to. The result in those cells is the actual sheet name.
Here's the simple part of the code so far:
Sheets("MM Ades 64z").Select
Range("B8:B59").Select
Selection.Copy
Sheets("MM Pnch 64z").Select
Range("B8:B59").Select
ActiveSheet.Paste
Sheets("MM Ades 64z").Select
Range("D8:D59").Select
Selection.Copy
Sheets("MM Pnch 64z").Select
Range("D8:D59").Select
ActiveSheet.Paste
Sheets("MM Ades 64z").Select
Range("J8:O59").Select
Selection.Copy
Sheets("MM Pnch 64z").Select
Range("J8:O59").Select
ActiveSheet.Paste
Sheets("TMMC").Select
Range("M1").Select
End Sub
View 9 Replies
View Related
Jun 1, 2006
how to allow formating a text and cell in protect sheet ?
View 4 Replies
View Related
Nov 24, 2008
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")
View 3 Replies
View Related
Aug 22, 2012
We have a spreadsheet on a server location with macros. Different users will access the spreadsheet from one pc on different shifts. Is there a way to set the area as a Trust Location for all users on the PC or enable the macros in the spreadsheet to execute for all users without having to set up the spreadsheet trust location or macros for each user? I would like to have the spreadsheet accessable to any user on the PC without setting up individual access. The users can change for shift to shift.
View 2 Replies
View Related