Unhiding Sheets With Combobox Selection And Duplicating Sheets Automatically
Sep 27, 2011
I have 25 sheets in the workbook and a combobox on the main page, The combobox references a range of 1-25 that represents the 25 hidden pages. right now i can get the sheets to unhide one at a time based on the selection e.g. combobox option 1 will unhide sheet 1 but the sheet are representing sites in a design so i need to have the option to select multiple sites in the combobox option so for example if i select 5 then sheets 1-5 should unhide. I hope I've explained that clearly.
The other question or option would be to just duplicate sheet 1 based on the combobox selection e.g. selection 5 duplicates sheet 1 5 times.
View 9 Replies
ADVERTISEMENT
Jun 27, 2013
I have the below VBA code associated with a sheet called "Select View". When the workbook opens there is another code that displays only "Select View", all the other sheets are hidden. In "Select View" there is a data validation list. Based on the selection the user gets to see only certain sheets. For example, if the user selects "Region 1" they see only the sheets "Region 1", "Panama City" and "Pensacola".
As you can see some of the names in the "Target.Value" are the same as sheets in an array. The ones that do are lead schedules, that is information from other sheets in an array roll into it. That is values from "Pensacola" and "Panama City" flow into "Region 1", the lead sheet.
What I am trying to do is finish the code where based on the "Target.Value" selected it automatically takes the user to the lead sheet. For example, when the user selects "Region 1" in the data validation it takes the user directly to the sheet "Region 1". The user of course can also see the other sheets in the array, that is "Pensacola" and "Panama City" and can manually select the tab to view or go into one of the later
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet
If Not Application.Intersect(Range("B10"), Target) Is Nothing Then
For Each sh In Sheets(Array("Panama City", "Pensacola", "Region 1"))
sh.Visible = Target.Value = "Region 1"
Next sh
[Code]....
View 6 Replies
View Related
Jun 18, 2007
I have a workbook that contains approx 50 sheets and will grow to somewhere in the region of 200.
The majority of sheets, which contain the raw data referenced by the renaining sheets, are hidden. I will occasionally need to update the data in some of those hidden sheets and would like an easy / quick way of unhiding them.
The front page has several comboboxes which select the page needed for the calculation being performed, eg I select Chapter2 in the first combobox, section 4 in the second and page 12 in the 3rd. The output is combined / abbreviated into into a cell eg Ch2-Sec4-P12. That being the name of a sheet I then use INDIRECTs to retrieve the data I want and place it in a spare sheet, that works well.
I'd like to do the same to select the sheet to unhide. I can setup the comboboxes to give the name of the sheet I want to unhide / hide in a cell but then I'm stuck;
How can I use the contents of a cell in place of the sheet name in a macro command such as Sheets("data").Visible = Not (Sheets("data").Visible) ?
View 9 Replies
View Related
Feb 24, 2009
My database has what I call the 'cover page'. On the sheet is a command button that will take the user to the next page. Basically what I want to do is upon opening the workbook, the user is taken to the cover page, and all the rest of the sheets remain hidden until they click a command button to 'enter' the workbook. P.S. I have some sheets that need to remain hidden though...
Of course the other way is just to have a userform that opens when the book is opened to do the same thing. Where would I put the code for the userform, in ThisWorkbook or a separate module?
View 2 Replies
View Related
Apr 27, 2012
I would like to be able to hide the tab i am in and unhide another tab at the click of the button,
I have tried recording the macro myself and it works up to a point,
I want it to finish on the tab i have just unhidden but for some reason it doesn't seem to do that (even though that's the way i recorded it)
View 4 Replies
View Related
Jul 20, 2009
Regarding the age old problem of trying to ensure macros are enabled in a workbook, using the process of hiding all sheets bar one:
Automatically Close Workbook If Macros Are Disabled
Reafid inserts some excellent code so that worksheets are hidden BOTH before closing AND on a normal Save event (I have also attached Reafid's zip file with the workbook that includes his/her code).
Having done much searching it seems to be the best solution out there, however I can still break it fairly easily and I'm worndering if anybody can stop me doing the following to break it:
Open the file in its restricted state (i.e. only Warning tab shown, all other sheets very hidden) and NOT enabling macros on opening. I'm therefore at the position Reafid intended; macros are disabled so all sheets are very hidden. Then to break it, in my personal macro workbook I have a simple macro that unhides all worksheets in ThisWorkbook, I run that personal macro and everything in Reafid's file is unhidden.
Reafid's file therefore has been opened with macros DISABLED and now all worksheets are visible.
View 9 Replies
View Related
May 5, 2006
I have groups of data in A5:AW200 with various blank rows in between, and would like to be able to automatically hide and unhide (toggle) the blank rows, but only for those rows where cells in column A are blank.
I'd also like to hide those same groups of blank rows on Sheets 2-5, which are laid out exactly the same as Sheet 1, but since they contain cell references to and are mirrors of Sheet 1, certain rows on those sheets may appear blank, but in fact are not. (I should probably have separate buttons on each sheet, but not sure). I know Sheet 1 can be used to test for conditions (blanks rows) and translate the results to Sheets 2-5, but I'm not sure how to do it.
View 4 Replies
View Related
Jan 4, 2013
I am trying to find code that will allow me to hide a set number of rows based on the value of a specific cell which I need to work for two worksheets in the same workbook. Is that even possible?
For example: when i enter 5 into cell D1, I need five rows to be visible on both sheets.
View 9 Replies
View Related
May 16, 2008
I am working on a spreadsheet for a client. I am using Excel 2007 and she is using Excel 2000. I have created a emplate for her to enter survey data into. Using the following code, I have created combo boxes for the drop-down menus so the client can tab from one cell to the next while entering data:
Option Explicit
Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
'Hide combo box and move to next cell on Enter and Tab
Select Case KeyCode
Case 9
ActiveCell.Offset(0, 1).Activate
Case 13
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub.....................
View 6 Replies
View Related
Sep 29, 2006
I am currently trying to add some functionality to an Excel workbook and I have a combo box that I am unable to get the values to populate. On the same worksheet I have a command button. Here is the code I am using to attempt to populate the combo box:
Private Sub cmdSendSave_Click()
Call SendSave
End Sub
Private Sub bxLocation_Change()
With bxLocation
.AddItem "Mt. Hope"
.AddItem "Summersville"
.AddItem "Huntington"
.AddItem "Pulaski"
.AddItem "Coastal Bend"
.AddItem "Odessa"
.AddItem "Wheeling"
.AddItem "Hollywood"
End With
End Sub
View 9 Replies
View Related
Aug 19, 2013
I wanted to know if there is any function/macro/option available which can hide/show sheets based on the value selected in the particular cell.
Basically i will have a table of contents as the first sheet. Post that if 'Yes' is selected against a particular line item, the sheet for the same should appear. If 'no' is selected the sheet would remain hidden. The sheets would be linked via hyperlink to the particulars (name) for each line of the table of contents. If required i can also remove the hyperlinking of cell.
View 6 Replies
View Related
Feb 10, 2007
Is there any way this code (below) can be modified so that the macro runs without actually selecting the sheet it requires. The reason for this is so i can hide the sheet (Column Output 3) from view so the data isnt displayed. at the moment the sheet is on view and i would rather it werent. this is the code i have
Sub DistributeRows()
Sheet11.Select
Cells.Select
Application.CutCopyMode = False
Selection.ClearContents
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim rngCrit As Range
Dim LastRow As Long
Set wsAll = Worksheets("Column Output 2")
LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
Set wsCrit = Worksheets.Add
Set wsNew = Worksheets("Column Output 3")..............
View 5 Replies
View Related
Sep 4, 2006
in the WorkSheet_Activate for Sheet 3, which changes the sheet name to the ComboBox Value. Is it possible to use the default name of the Sheet, in this case Sheet 3, if the ComboBox Value = "0". I have the following codes for a ComboBox & Userform:
Private Sub ComboBox1_Change()
Sheet3. Range("E1").Value = ComboBox1.Value
End Sub
&
Private Sub Userform Initialize()
ComboBox1.AddItem 0
For x = 1 To 60 Step 1
ComboBox1.AddItem Format(x, "0")
Next x
ComboBox1.Value = Sheet3.Range("E1").Value
I also have this code ..............
View 2 Replies
View Related
Sep 20, 2006
I have a form (userform) which has a combo box and this combo box functions as a selector. I mean, when the person chooses one of the items (sheet1, sheet2, sheet3, etc.) inside the combo box, it views the sheet he/she picked. How will I do that thing? What code/s will I write in my module?
View 4 Replies
View Related
Mar 25, 2013
I was wondering if there is an easy function that would copy only some part of a list as if like I select a category.
Let me elaborate. As in a previous post i have a list of apartments and a set of categories (locations, tube stations, number rooms, distance to tube stations, etc).
I want to get all the items that are the same from a category and post them automaticly on another sheet to single them out, for instance all of them that are on Liverpool Street station or all of them that have 2 rooms.
Is there a simple way to do this or do I have to get a macro to read my whole list? I ask cause I am continuasly updating the list and don't want to recreate the formula all the time.
View 6 Replies
View Related
Sep 13, 2012
I have a name list on sheet2, column A that will have names added to and removed from. Everytime this list is modified it sorts in ascending order and creates a new sheet named after the name added. These items are added to an ActiveX combo box on sheet1. I need to know how to link the item in the combo box to the corresponding sheet(ex: item: "Jim", sheet name: "Jim").
View 2 Replies
View Related
May 8, 2014
That particular question has been solved, but now i need it to work with multiple values from combobox.
So for example,
if "Master" is selected in combobox3, it will delete sheets 7, 8
if "CSR" is selected in combobox3, it will delete sheets 1, 8
if "Original IND" is slected in combobox, it will delete sheets 1,7
The current code will work when "master" is selected, however i am getting compile errors when i select "CSR" or "Original IND"
WhenI select CSR, it does create a new wb for me (without sheets 1,8) correctly but i get a Run time error.
Run-time error '-2147417848 (80010108)': Automation error
The object invoked has disconnected from its clients.
When Original IND is selected, i get a compile error and it highlights 'Make the new workbook active newWBK.Activate
View 5 Replies
View Related
Jul 16, 2014
Problem- I need a combobox on sheet1 to reference a column in sheet2 and return only a distinct list.
[Code].....
If I put my sheet2 column info on sheet1, and then reference it appropriately as built in the code above, it code works great. So I know it's close. I cannot change it to work with 2 sheets though. I've tried to "set wsSheet2 = wbBook.Worksheets("Sheet2")" and call it 'where I think it's appropriate', which is the problem. I don't know what's appropriate.
changing the steps in the code above, where in my case the combobox is on sheet1, and the range/data referenced is on sheet2?
This is what I've changed the original VBA to, and when I run the debug, it picks up the last "with...combobox1" statement as the error (error 1004, app/object defined error):
[Code] ......
View 1 Replies
View Related
Jun 7, 2011
I am currently using this code to place names in a combobox (active X). How can I use an array to perform each line to all 12 months without repeating the code over and over?Here is what I have:
Code:
Sub auto_Open()
Sheets("FEB").ComboBox1.List = Array("ALL", "ACT", "ROF", "MM")
Sheets("FEB").ComboBox2.List = Array("ACTvsROF_2", "ACTvsPLN_2", "ACTvsLY_2", "ROFvsMM_2", "ROFvsLM_2", [code].....
View 2 Replies
View Related
Feb 9, 2010
Sub ComboBox1_Chg()
For Each Sheet In Worksheets
If Sheet. Name <> "CoverPage" And Sheet.Name <> Sheets("CoverPage").ComboBox1 Then
Sheet.Visible = False
Else: Sheet.Visible = True
End If
Next Sheet
End Sub
It works if I step through it (F8) but the ComboBox doesn't work. It's named ComboBox1, and in the properties the ListFillRange shows all of the names in the list in the ComboBox correctly.
View 5 Replies
View Related
Jul 29, 2009
I would like to do is have 2 listboxes. In the first listbox I would like the name of all the worksheets which contain the words "elective class: " in cell C7.
In the second listbox I would like the name of all the worksheets which do not contain the words "elective class: " in C7. I need this only to source from worksheet 7 onwards however.
The listboxes are called ListBox1 and ListBox2 respectively.
Also I am using this to print and I have a button which currently selects all the data in the first listbox and another button which prints all the selected data. The code being used for this is:
View 2 Replies
View Related
Feb 5, 2014
How do I get sheets names automatically? I.E. I want to get in A2 sheet number 2 name. In A3, sheet number 3 name...
View 1 Replies
View Related
Jun 6, 2014
I tried to search the forum for my unique problem but had no luck. As you will see attached, I have a series of excel buttons I need to duplicate and have target a different set of cells. I am hoping there is a way to avoid manually doing this.
The goal of the document is to push the button when both colours in the row and column interact (research on birds). There are two additional behaviours with an exact same set of buttons but they need to target "Body Rush" and "Food Displacement" tables underneath. I need all the buttons on one page since multiple behaviours happen simultaneously that need to be recorded.
I have something like 100+ modules in VBA I have created, I am hoping there is an easier way to do this so I don't have to create another ~200 modules in order to get the last two behaviours setup.
I am hoping there is either an easier button system or way to make buttons adjust somehow.
I am not the most advanced VBA user
View 6 Replies
View Related
Jul 20, 2013
I am working on a macro which transfers data from one sheet to another. The code starts by taking cells M1:P1 from sheet SL_Background and moving them to B9:E9 on sheet CreateSL. It then moves to the next set of four cells on SL_Background (Q1:T1) onto the next line down on sheet CreateSL (B10:E10). I am repeating this process about 180 times right now and it is all coded like this:
VB:
Sheets("SL_Background").Select
Range("M1:P1").Select
Application.CutCopyMode = False
[Code]....
So what I would like to do is to make something that automates this process and will eliminate more than 1000 lines of code.
View 9 Replies
View Related
Nov 30, 2008
I have a sales sheet that records every sale as it comes in. I want it to copy the relevant information (only that sales reps sales) into each sales reps individual sheet automatically so that I can send each rep his own sheet. Can this be done? SEE ATTACHMENT
View 2 Replies
View Related
Dec 5, 2007
I have made a daily planner for each month. The dates are on separate sheets which are titled 1st Dec 07, 2nd Dec 07 and so on.
This is also included on the actual sheet as well in the form of a heading. I want to know if it's possible to automatically rename the text on the sheet to what the sheet is called?
Or if the first sheet has a title of '1st Dec 07' how can I get the other sheets to automatically be titled in sequence so I don't have to do it manually?
View 14 Replies
View Related
Nov 25, 2008
I'll be as succinct as I can, and I'm sorry if this question has been answered already. I've had a look at past posts and can't seem to find anything.
My service has an Excel workbook of patient details. These details are separated across three sheets called 'Generic', 'Heart Failure' and 'Falls and Fracture Management'.
The columns in each sheet are the same. Every month I produce a single report of data from columns A, B, L, Q and R in each sheet.
I've been doing this by copying/pasting each of those columns from 'Generic' individually into a new sheet, then putting the same data from 'Heart Failure' underneath, etc., then manually deleting all rows that contain a blank in any cell.
Is it possible that VBA code can be written to do this? I don't know anything about codes; I know I could achieve the same effect with linking, but it seems that this would be unreliable.
View 12 Replies
View Related
Jun 30, 2012
I am trying to copy data from three separate tables (from sheet 1, 2 and 3) into 1 table (sheet 4). I do not want to combine the data, instead have all of the data in one place.
I have tried the consolidate function, but am not having much luck?
View 6 Replies
View Related
Sep 14, 2009
Our company has designed a Excel Document for quoting purposes. The problem we are having is that certain sheets that are needed to do the quote do not need to be used after the quote is finished and saved. These extra worksheets are taking up a lot of room on our server. In the document I have already disabled the save feature so that the saveas has to be used. Is there code that can delete sheets 3, 5, 6, 7, 8 upon doing a save as?
View 4 Replies
View Related
Dec 28, 2006
I would like to be able to use the before save event to hide some sheets before the save then after unhide some sheets. So that the user carrys on with the sheets they had before saving but when the document is reopened the correct sheets are hidden.
This is what I have so far but unfortunately when you click close and then save changes it runs the before save code and then goes around in circles, reasking the user if they want to save changes
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Cancel = True
Application.EnableEvents = False
Sheets("Protected Content").Visible = True
For Each ws In Worksheets
If ws. Name <> "Protected Content" Then ws.Visible = False
Next ws
Me.Save
For Each ws In Worksheets
ws.Visible = True
Next ws
Sheets("Protected Content").Visible = False
Application.EnableEvents = True
End Sub
View 3 Replies
View Related
