Hide/Show Sheets Chosen In ComboBox

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


ADVERTISEMENT

Conditionally Show / Hide Combobox Based On Cell Value

Jun 5, 2013

I'm quite new to VBA, but I am attempting to get a Forms ComboBox to appear or disappear based on whether a certain cell (P7) reads YES or NO. P7 in turn updates in turn based on a user-selected value. As of now, the ComboBox only appears or disppears if I go back in and out of the formula I entered into P7. Basically, I want my ComboBox to dynamically update based on the value in P7. That may sound a little muddled, so here is my code for the ComboBox:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("P7")) Is Nothing Then
If UCase(Target) = "YES" Then
Me.Shapes.Range("Drop Down 30").Visible = msoTrue
Else
Me.Shapes.Range("Drop Down 30").Visible = msoFalse
End If
End If

End Sub

View 3 Replies View Related

Excel 2010 :: Hide / Show Textboxes And Labels Based On Combobox Selection?

Jul 8, 2014

I have created a UserForm that has a ComboBox and depending on the number selected I want it to show that number of Labels/TextBoxes...

So if I select "0" nothing is shown, if I select "1" one set of Labels/TextBoxes is shown, select "2" and two sets of Labels/TextBoxes are shown... but also if I have selected "2" and then select "1" I want the second set to be hidden again...


Also I know I should have renamed the Label/TexBoxes to make it easer but I was adding things and making it up as I went along...

I'm using Excel 2010 on windows 7.

Code:
Sub UnHide_NewRoutings()
If (Engineering.ComboBox2.value) = "0" Then
Engineering.Label4.Visible = False
Engineering.TextBox5.Visible = False
Engineering.Label9.Visible = False
Engineering.TextBox9.Visible = False

[Code] ..........

View 3 Replies View Related

Macro To Unhide / Hide Sheets With Combobox Selection

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

Show / Hide Sheets Per Username

Mar 8, 2014

I'm trying to only show specific sheets per user using the environ variable and this code seems to work for the single user / sheet but the master user does not function correctly i.e. the code does not show all sheets, this is the code I am using:

[Code] ......

Why the above code does not respect the Master User "Jane" should be able to see all sheets?

Original source for this code was found here:

HTML Code:  [URL]....

View 7 Replies View Related

Hide & Show Sheets Automatically

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

Hide/show Multiple Sheets By Macro?

Oct 29, 2008

I got a quite huge excel file with multiple sheets. For convenience sake I want to group and hide all the sheets not necessary for the viewer.

View 14 Replies View Related

Hide & Show Sheets Based On Names

Dec 19, 2006

I made one file with 13 sheets.

sheet1 tab name is : MAIN
and other sheet tab name like following
2. xyz-Sales
3. xyz-Rev
4. xyz-SSN
5. xyz-ddn
6. abc-Sales
7. abc-Rev
8. abc-ddn
9. abc-ssn
10. ddd-sales
11. ddd-Rev
12. ddd-ssn
13. ddd-ddn

In Main sheet There are 3 buttons

1 . XYZ
2. abc
3. ddd

when user press on xyz button then only xyz sheets (like sheet 2 to 5) are shows to user and other sheets are very hide

if user press abc button then only abc sheets (like sheet 6 to 9) are shows to user and other sheets are very hide

i don't want to use

Sheet2.Visible = xlSheetVeryHidden

i want to use finde xyz sheet tab name and shows and other are hide.

View 9 Replies View Related

Show / Hide Sheets Based On Values Of Cells In Range?

Sep 11, 2013

I have a workbook wherein I have 7 sheets.Lets say they are called Tom, Peter, John, Sia, Mia, Tia and "Home Page". I have 2 buttons for Report 1 and 2 to which I want to assign the macros.I also have a table wherein I have defined which sheets I want to show. First Column of table has sheet names from A2:A6(Home Page,Tom, Peter, John, Sia, Mia, Tia). Column 2 has report 1 sheets - Home Page, Tom, John, Mia and Column 3 has report 2 sheet names- Home Page, Peter, Sia, Tia

What I want to do is, if I click on "Report 1" button, I only want to show sheets whose names are there in cells under report 1 so for report 1 it will be Home Page, Tom, John, Mia. For Report 2, it will be Home Page, Peter, Sia, Tia. Since I have many reports I want this to be one macro. Stepwise, here is what I want

1. Click on button for Report, macro should check which report I am referring to and select the range on basis of that. Report 1 = column B, if Report 2, Range is column C.
2. Basis the range I want sheets to show or hide.

View 1 Replies View Related

Hide/Show Sheets Based On Cell Values & Validate Entry

May 30, 2008

excal VBA programming.I have attached the file name "help" for your easy explanation purpose.

1. Is it possible to hide sheet nos. 1,2,3,4 & unhide the sheet as wished by me by puting the value (1or 2 or 3 or 4) in B3 cell.

2.There are per day production rate in E18 to E22 cell. Now whenever I will give value in H18 or H19 or H20 or H21 or H22, it will check whether the value is same with the respective E 18 or E19 or E20 or E21 or E22 cell. If both the values are not equal then give a message box "WARNING!!! YOUR VALUE IS NOT SAME". Can it be possible by creating VBA programming.

View 5 Replies View Related

Go To Range Chosen: ComboBox

Oct 28, 2006

I need to go to named cells in a workbook based on a selection in a combobox - I just can't seem to work out the coding -

View 4 Replies View Related

Copy Chosen Combobox Value

Jul 19, 2007

I've created drop down box using VBA code. Data for drop down box is on the Sheet2 and drop down box created on Sheet1. Need code I have to use to copy selected value from box to any cell on Sheet1 (i.e. Sheet1.A5). Here is my

Private Sub Workbook_Open()
CreateMyBox
End Sub

Sub CreateMyBox()
Dim MyBox As Excel.Shape
With ActiveSheet
Set MyBox = .Shapes.AddFormControl(xlDropDown, 5, 17, 175, 15)
End With
With MyBox
.Name = "MyBox"
.Fill.ForeColor.RGB = RGB(255, 0, 0)..................

View 7 Replies View Related

Return Chosen Value From ComboBox Control

Oct 25, 2006

I am trying to find a formula that references a cell on a spreadsheet (H7) which is really a Combo Box that is located on a range of cells K25:K30. I want to put a formula in H18, but of course, the combo box always references the range on K. i have tried the $K$25:K430, but i don't know what i am doing. The drop down menu and everything works fine, but the data IS stored elsewhere. HOW do i tell Excel to look at K25:K30,(depending on choice within combo) and then ad H8:H19? I have looked, but all answers are for forms. I am doing this for final exercise for a university course, and (hmmhmm) must folllow obtuse instructions.

View 7 Replies View Related

Find Chosen ComboBox Item

Dec 28, 2006

I have, 10 combobox, if the user makes click in the combo,start the event combobox1_change, and the value of the combobox is searching in excel, when find it, move one cell toward the cell of the left, and the value of the cell of the left is shown in a label, that work.

But I need copy teen time the same code? (My english is very bad)
this is the

Private Sub ComboBox2_Change()
If Sheets.Application <> "Materiales" Then
Sheets("Materiales").Select
End If

Range("H:H").Select

Cells.Find(What:=ComboBox2.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

View 3 Replies View Related

Check If ComboBox Has Item Chosen

Jul 27, 2007

I have a userform which manipulates data based on the userselection from the combo box. I have setup the userform so that the user may select up to 3 sheets due to the presence of 3 combos boxes. I need to writing an IF statement which checks to see if combobox1 is occupied to carry out a function, followed by it checking to see if combobox2 is occupied to carry out the function, and then checks to see if combobox3 is occupied and carries out the funciton.

As such, if only 1 combo box is occupied it would then only carry out the operation on combobox1's selection, and if none are occupied, nothing occurs, the box simply stays open. This is what I have so far, I know there is probably a more eligant way of writing such a If/Then/Else statement

Sub Start()
If UserForm1.ComboBox1.Value And UserForm1.ComboBox2.Value > 0 Then
Call Find1
Call kTest1
End If

If UserForm1.ComboBox3.Value And UserForm1.ComboBox4 > 0 Then
Call Find2
Call kTest2
End If................

View 3 Replies View Related

Return Corresponding Data Chosen From ComboBox

Sep 9, 2007

I have a combo box, which is used after a search. The box only identifies a list of cells, but to the right of that, is somemore info that i need.

I would like it, so when you bring down the list window, and pick from the list, it knows which Address it is, so it can then HLOOKUP the information in the next column.

I can't seem to make it happen. I either cannot get it to let me know the Address of the cell I had choosen, or I cannot make it look to the right, and put the contents into another text box...

I have something like this:


Addresslist.plant = addrlist.offset(,5).value

how do i make it find the location ie $D$5 of the of item i selected from a combo box?

View 9 Replies View Related

Get Chosen Value From User Form ComboBox

Sep 11, 2007

Right now I am constructing a macro so that upon exection, the user will be forced to select one of the dropdown menu options which are listed in a dynamic array. The dropdown menu should be in a popup of somekind created by the macro and not on any of the worksheets or charts. I would like to then assign the choice that the user makes to a variant. I have searched the web but not found what I am looking for and was hoping that someone could give me some phydocode that I can look at.
The restrictions that I am operating by do not allow me to place a combobox upon any of the worksheets which is what I find in all the examples posted online.

View 4 Replies View Related

Determine If ComboBox Item Chosen

Mar 17, 2008

How do I check the information from a user selection of all comboboxes on a multi-page control to format a worksheet?

A few more specifics: There are roughly forty comboboxes on a multipage with six tabs. The comboboxes contain a list of choices for how different aspects of the project are financed. I want to check for whether the user has selected a specific entry. If any of the forty comboboxes have made that selection, some code runs that formats the column of the worksheet in a specific way. I have written the code which formats the column, and it works fine, but my attempts to run the check mentioned above, have not worked. The code cannot check based on .listitem, it must check based on a specific string.

A few more clarifications: It doesn't matter whether all forty comboboxes have this selection, or one; if any of them have the selection, the code needs to recognize this. The code would currently run off a command button which performs a series of calculations, tests, and then runs the code to format the worksheet.

View 8 Replies View Related

Column Number Of Chosen ComboBox Value

Apr 24, 2008

I'm trying to build a Userform with a combo box that is populated by a row of data, this will allow the user to select the column of data. What i want to do is find out the address of the data that is selected so I can work with.

ie
A1 = Apple
B1 = Orange
C1 = Grape

if the user selects "Grape" I want to find out the column that it is in. Also the Row of data is Dynamic so I need to re-check the range each time.

View 8 Replies View Related

Set Value Of ComboBox Based On Chosen Item From Another

May 15, 2008

I have two comboboxs on a userform, both are populated like this:
(ComboBox1 is a different sheet/column)


' Sets Remarks in ComboBox4 Contents
With Worksheets("Data")
Set rng = . Range(.Cells(1, "C"), .Cells(Rows.Count, "C").End(xlUp))
End With
With ComboBox4
.RowSource = rng.Address(external:=True)
End With
The function is that ComboBox1 will populate the names on lets say Sheet1, Column A, and when selected will populate by offset all the other Textboxs, and ComboBoxs.

Textbox1 is a date
ComboBox4 is populated off of items from the data sheet
ComboBox1 is populated off of sheet1 and provides names, then fills the userform fields

In populating the Userform, it fills Combobox4.value by the offset value of the selected name.

That cell does not contain the same info that was loaded into the ComboBox initally, and it does not show it. All other ComboBox entries match preloaded values, and show.

How do I get the ComboBox to display what is in the OffSet cell value, rather than blank because its different?

View 9 Replies View Related

Fill ComboBox Based On Item Chosen In Another

Sep 8, 2006

Not overly familiar with ComboBoxes but what I want to do is load a ComboBox with data based upon the selection of another ComboBox

Please see attached example.

ComboBox1 - I can get to load.
ComboBox2 - I want to load but only those lines that match the above selection
TextBox - Load with the data on row selected by ComboBox2

View 9 Replies View Related

Combobox Validation: Only Listed Items Can Be Chosen/Used

Oct 2, 2006

I have a Combobox with it's 'RowSource' set to two columns x 1500 rows on a spreadsheet. The Combobox is set to 'fmMatchEntryComplete'. When the user types in invalid text I get an "Invalid Value Property" error. I would like to validate the Combobox so that the user cannot type text other than available in the list, or not allow for the focus to be taken away away unless the item is a match to the list. At present due to the interaction of other controls on the userform, the only way to clear the error is by pressing the 'Escape key'. I have a button designed to reset the 'RowSource' of the Combobox but even after adding a 'MouseMove' event to this button with code

Application.SendKeys "{ESC}"

View 4 Replies View Related

Go To Cell Housing Chosen ComboBox Item

Nov 28, 2006

I have a sheet with several entries. I want to find a way to have the user go to a specific cell instantly. What I thought I'd try was a combobox that when I click on a name in the combobox. It will make the matching name from the list the active cell.

View 9 Replies View Related

Color ComboBox Based On Item Chosen

Sep 27, 2007

Is it possible to have a combo box in excel, where, when an item is selected, it is assigned a colour depending on which item it is? Eg, The combo box list has item 1, and item 2. If I select item1, then the text becomes red, if I select item 2, the text becomes blue. If not possible using combo box, what method can I use?

View 2 Replies View Related

Remove Chosen ComboBox Item Plus X Rows

Feb 2, 2008

I have a dynanic range named Room on B1. My combobox1 rowsource is linked to the Dynamic range Room. I would like to be able to delete the the specified selected room from the combobox and the next 3 column C,D,E (delete Shift cells up)

View 2 Replies View Related

Conflict Between Auto Save&close Macro And Show/hide Sheets Macro

Oct 16, 2009

I am trying to make a save&close workbook macro.

I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).

The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.

If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.

The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)

The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.

View 10 Replies View Related

Join Chosen ComboBox Text With All Used Cells In Range

Oct 16, 2007

I am trying to find a way to create a macro that will take the data selected from the combo box and concatenate it with the data found in cells.

For Example:

Combo Box Selection - "Test"

ID (Column A) - Before
12345
98765
99999
55555
empty cell
23232

ID (Column A) - After
12345 Test
98765 Test
99999 Test
55555 Test
empty cell
23232

View 9 Replies View Related

Control Toolbox ComboBox To Return Chosen List Number

Aug 18, 2006

I'm using combo boxes. Initially I used combo boxes from the Forms toolbar, however the text in the combo box was to small. Now I'm using combo boxes from the Control tool bar. However, i would like the link cell to show the number of the entry in the list (like the forms control box) and not the actual entry. Is there an option I need to select in properties, or is there some VB code I can attach to the combo box ?

View 4 Replies View Related

Macro To Hide Rows When A Check Box Is Chosen

Aug 26, 2009

Need a macro to hide two rows when a check box is checked? Is this even possible? I would like rows 44 and 45 to be hidden when the check box next to loan impairment is checked.

View 9 Replies View Related

Hide Unchosen Objects/Shapes When Another Is Chosen

Nov 30, 2008

I work for Local Governments where supervisors want me to create Project Update program.

I've created one with VBA but still, cannot get the unchose color circles in the same row to become blank.

Whenever they choose one of the 3 color circles, 2 becomes blank.
I hope this will explain well enough for you to understand

here's the code I've put in and with the attachment, you can see my explaination.

Sub TimeRed()
doit 3, "D"
End Sub
Sub TimeYellow()
doit 6, "E"
End Sub
Sub TimeGreen()
doit 4, "F"
End Sub
Sub BudRed()
doit 3, "H"
End Sub

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved