Set Control Visibility Based On Tag Property

Jan 15, 2008

I have a userform which uses the tag property of the frame to determine whether a frame is visible (and hence the controls that reside within the frame). A frame contains three combo boxes, and six text boxes. The tag property of the frame matches the number of frames that are visible on the form, so that if the user selects five frames, frames 1 through five become visible and for all other frames visible = false. A frame contains all the data for a single entry. There can be up to fifty frames/ entries that are visible on the form depending on the user selection.

I want to use the visibility property to do two things: first the combo boxes are filled from an array after the user selects the number of frames (or entries). I only want to fill the comboboxes where the frame is visible. The second thing is that I have a function which uses the data from the text boxes and combo boxes as required arguments. Since these are required arguments, I will get a data mismatch error if I try to call it and the controls are empty. Therefore, I only want to call the function if the frame which houses the controls is visible.

Public Sub Visible1(Entry As String)
Dim ctrl As Control
For Each ctrl In UserForm2.Controls
If TypeName(ctrl) = "Frame" And ctrl.Tag <= Entry Then
With ctrl
.Visible = True
End With
Else
If TypeName(ctrl) = "Frame" And ctrl.Tag > Entry Then
With ctrl
.Visible = False.............

View 6 Replies


ADVERTISEMENT

Cannot Set Control Source Property

Aug 31, 2007

I am trying to set a controlsource property so that when a cell on a worksheeet is changed, the label will also change. However, when I try to set the controlsource I get an error that says :

Could not set the ControlSource property. Invalid property value.

I've tried it with a label in the code,

lblUser.ControlSource = Worksheets("User List").Range("C1")

and I've tried it in the properties window of a textbox, and I cannot get either to work. I've also unprotected the sheet first, thinking that might have something to do with it.

View 5 Replies View Related

How To Use Transition Property For Multipage Control

Jun 1, 2012

I'm trying to use the Transition property for the multipage control without much luck. So far I have a userform with a multipage control on it.

Code:

Private Sub UserForm_Initialize()
With MultiPage1
.TabOrientation = fmTabOrientationLeft
.MultiRow = True
.TabFixedHeight = 30
.TabFixedWidth = 80
.Style = fmTabStyleButtons
.Value = 0
.TransitionEffect = 2 ' error here "object doesn't support prop"
End With

or...

Code:

UserForm1.MultiPage1.TransitionEffect = 2 ' same error

View 2 Replies View Related

Set Variable To Forms Control & Get Value Property

Mar 22, 2008

How do I reference a spin button from a variable that I set.

e.g

Dim objSpn As Object
Dim y As Integer

Set objSpn = ActiveSheet.Shapes("spnWCDate")

y = objSpn.Value

This doesn't work, but hope it explains what I want. It's so I dont have to write the whole object name each time I reference it.

ADDED: I've just noticed that this doesn't work even i do reference the object with the full name. I copied this from a forms spin control that I was using, why can't i store the value of the spin button in a variable?

View 4 Replies View Related

Evaluate UserForm Control Where Name Is In 1 Cell & Property In Another

Jan 29, 2009

In A1, other code inserts the name of a Form control (textBox, comboBox etc), and in B1 it inserts the name of a Property that is always valid for the A1 control. I want to concatenate these two items to produce a formula in C1 that evaluates the current value of the Property on the running form and continues to update with each recalc. It's ok if I have to force a recalc to get the latest values.

The code feeding the items to A1 and B1 and which will be harvesting the Property values from C1 is running in the same Form that holds the controls being referenced.
So if A1= "Label1" and B1 = "BackColor", then in C1 I'd like the same result as if in VBA I said X = Label1.backcolor. I think that what I need is the Evaluate function, but I've read Arron's article on it I just can't seem to make it work here.

View 3 Replies View Related

Create Loop That Changes CheckBox Visibility Based On Value In ComboBox

May 28, 2014

Trying to create a simple loop that will change the visibility of a large quantity of CheckBoxes when a certain value is selected within a ComboBox . I'm very new VBA programming and loops are not something I've tried yet. My code currently is this:

[Code]......

When I run it, I get a "Compile Error: Invalid qualifier".

View 2 Replies View Related

Generate Property Name Based On Address?

Nov 4, 2010

I'm trying to generate a property's name based on an address. I thought the index/match functions would best serve my purpose, but here's my problem.

The address I'm using to search may not exactly match the table I'm looking in. For instance:

The address I'm using to search is 456 E. Bell Rd.

The address in the table is 456 EAST Bell Rd.

View 6 Replies View Related

Change Visible Property Of Check Box Based On The Value Of A Cell.

Feb 8, 2010

I have attached sample workbook that has a user form with 6 check boxes and 3 text boxes. The value of each of the text boxes is based on a cell value in Sheet2. I have the visible property of CheckBox5, CheckBox6 and TextBox3 all set to False. What I would like to be able to do each time the user form is opened is have the visible properties of those controls dynamically changed to True only if Sheet2 cell A3 has text entered in it.

View 2 Replies View Related

ComboBox Error "Could Not Get The List Property - Invalid Property Array Index" When Typing Out Of Range

Jun 9, 2006

I have a form with several combo boxes, and they function just the way I like as far as being able to pick from the list, or typing in them and having it show you the next available item in the list as you add letters. Whats happening that I would like to know how to deal with is... as soon as you type a letter that is not in my lookup range it generates an error. "Could not get the list property - Invalid property array index". I don't want people to be able to add to the list, but I would like a msgbox to pop up. Then allow them to go back to the box and try again.

View 2 Replies View Related

Button Visibility And Colour

Sep 24, 2007

I would like to make a command button invisible, and to change its colour. I have Googled but cannot find out how to do this - button1.visible=false returns an "Object required" error message

View 14 Replies View Related

Ensuring Visibility Of Text

Sep 10, 2006

I created an automation process where worksheets are generated. But after the sheets are generated I noticed that one of the company names is missing from the list. As I am watching the worksheets generate I can see the name quickly printing on the screen. When I use a message box to validate the data entry, the name does appear. After all is generated, the name is still missing. Does anyone know what this could be? Could it be a software malfunction?

View 6 Replies View Related

Hide Toolbars & Restore Visibility

Aug 13, 2007

I have a procedure that stores the Commandbar names and visibility settings in an array on open. Another procedure hides all Commandbars on open and restores their original visibility settings on close. For some reason the "Standard" and " Formatting" toolbars don't return to visible on close.

My error log says "08/13/07 18:21:37 [QA.xls]MCommandBars.bRemoveMenus(), Error -2147467259: Method 'Visible' of object 'CommandBar' failed". I have tried manually testing the hide/unhide procedure with hard coded settings and checked the correct variables are being sent and all seems to be correct.

I am pretty confident that the procedure to store settings is correct but have included the code just in case, also just noticed the "Standard" and "Formatting" toolbars are the only two visible at programme start.

Public gsaMenus() As String

Public Function bMenuSettings() As Boolean

Const sSOURCE As String = "bMenuSettings()"

Dim bReturn As Boolean
Dim iCounter As Integer
Dim cbrMenu As office.CommandBar

View 9 Replies View Related

Reset Visibility Of Accidentally Hidden Sheets?

Oct 19, 2012

I was playing with some codes on hiding and unhiding the sheets and then I tried this code to hide a sheet but then I decided to delete the code and the module after. The problem now is I can't unhide the sheet. Even the Insert/Rename/Delete sheet option when I right click it are all disabled.

VB:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheet2.Visible = xlSheetVeryHidden
Me.Protect ("password")
End Sub

View 6 Replies View Related

VBA Looping Through Command Buttons And Change Visibility?

Jun 11, 2013

I'm trying to write a code to make a number of buttons visible depending on a cell value

I have 10 command buttons all are invisible and I want to show only the first x x is the value of cell "A1" in "Sheet1" (will be from 1 to 10) Command buttons names are default names (CommandButton1, CommandButton2, ... , CommandButton10)

Note: I'm working with a worksheet not a userform

View 8 Replies View Related

Amending Sheet Visibility In Visual Basic?

Jan 28, 2014

Error message: Unable to set the Visible property of the Worksheet class

View 1 Replies View Related

Visibility Of Cell A1 On All Worksheets When Macro Finished

May 8, 2007

How can I loop through all worksheets in a workbook and, not only select cell A1 in each worksheet at the end of a macro, but have the visibility of cell A1, no matter where you last clicked on the worksheets, such as a remote cell like BK244.

I would like the user to see the first row and further most left cell (cell A1) when they click any sheet in the workbook, because my macro causes several rows of data to be written, leaving the viewer to see something like row 244, instead of the top row which is where the titles are.

View 5 Replies View Related

Embed Picture Into Worksheet & Toggle Visibility

Dec 18, 2007

I am trying to embed a picture into a worksheet, hide the picture, then call it up later when a macro runs. I know how to insert a picure using a macro, but I want to embed the picture so that I can take the file with me that includes the picture and be able to call the picuture in my macro. Right now, the picture is on my hard drive and the picture will not load if I take the file to a different computer and run the macro.

View 4 Replies View Related

Toggle Shape Visibility On Different Sheet Than Event Code

May 29, 2008

I have found that this only works on the sheet being viewed, how can I make it work on a different sheet than the one that I am on?

Option Explicit
Private Sub Worksheet_Calculate()
If Range("C1").Value = "5" Then
ActiveSheet.Shapes("rectangle 1").Visible = True
Else
ActiveSheet.Shapes("rectangle 1").Visible = False
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

View 8 Replies View Related

Range Object/property: Calculates 2nd Column Based On 3rd Column's Number

Jun 1, 2009

I'm trying to write a code that calculates 2nd column based on 3rd column's number or vice versa based on the condition set on the 1st column. Below, there are two procedures. "SimpleCalc" and "SimpleCalc2". I first wrote SimpleCalc2, but it isn't working, so I worked around the error by writing SimpleCalc, which selects a cell and moves around by offset. I personally find it hard to read and error prone as I develop more logic around it.

I'm trying to develop more function based on this code, so I need to make it neat and flexible. what I'm doing wrong in SimpleCalc2? Or do you have any suggestion to improve the code "SimpleCalc"? I'm using Excel 2003.

Sub SimpleCalc()

Dim SimpleMethodRng, SimpleMethod As String, i As Integer

ActiveWorkbook.Worksheets("Dashboard").Range("P5").Select

SimpleMethodRng = ActiveWorkbook.Worksheets("Dashboard").Range("N5:P12")

For i = 1 To 8

SimpleMethod = SimpleMethodRng(i, 1).........

View 3 Replies View Related

Value For Variable Based Off Control Checkbox.

Jan 26, 2007

I have several checkboxs in an excel sheet, that if one is checked i would like it to make a change to a variable in a macro that will run when saving a file.

for an example:

if checked:
Checkbox1 = NCE1
Checkbox2 = NCE2
Checkbox3 = NCE3
Checkbox4 = NCE4
Checkbox5 = NCE5

I have a bit of code that is like this....

ActiveWorkbook.SaveAs ("\marketing2PartageNonconforms" & sF1 & " - CO" & sF2 & " - FC" & sF3 & ".xls")
I would like sF1 to change depending on which checkbox is checked. so if checkbox1 is checked, than sF1 = NCE1 .

View 9 Replies View Related

Text Based Treeview Control

Nov 21, 2006

i found this quick online tutorial [url] on treeviews. i was trying to modify the code to make it a simple tree contaning just text like attached image, and to have a command buttton to add, and delete from the tree.

View 9 Replies View Related

Disable Control Based On Selection

Jul 17, 2007

I am working on a form in VB and have a combobox and three monthviews. My question is when a user selects a specific item from the combobox (ex. terminal illness), I want one my monthviews to disable.

View 4 Replies View Related

Specialcells Property Error "Unable To Get The SpecialCells Property Of The Range Class"

Apr 14, 2007

Having problems with trying to get my vba code to access the SpecialCells property. Receiving the following error.... Unable to get the SpecialCells property of the Range class. The section of my code is below that is causing the error. Keeps stopping on the "Selection.SpecialCells(xlsCellTypeVisible).Select" line.

Sheets(" Book Query").Range("A6:I6").Select
Sheets("Book Query").Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlsCellTypeVisible).Select
Selection.Copy
Sheets("Inventories and Variances").Select
Sheets("Inventories and Variances").Range("A7").Select

View 2 Replies View Related

Vba Cell Control Based On The Cells Contents

Sep 11, 2009

Is it possible to place formulas in vba to control cells based on the cells contents.

A2 will be the the price of an item excluding vat and b2 will be the price of an item including vat

What i want to achieve is if I put a figure into a2 b2 then multiplies a2 by 1.15 to reflect the price including vat. If this is achievable I then want to reverse the proceedure so if I put the price including VAT into b2 a2 divides b2 by 1.15 to reflect the price exc. VAT.#

if all of the above is clear and possible I would like to know if it is easily possible to copy the workings for a3, 4 ,5 ,6 etc and b3, 4, 5, 6 etc.

View 10 Replies View Related

Select Worksheet Based On ComboBox Control Choice

Sep 1, 2006

i have a user form with 4 combo boxes and one text box. what i've tried to do is make it so that if the month combo box reads january, then all of the info is placed in a worksheet called january. at the moment it is putting all the info inputted into one sheet. if some one could take a peek at the code below.

If cbomonth.Value = January Then
Sheets("January").Select

Range("A1").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) Or IsEmpty(ActiveCell.Offset(0, 1)) Or IsEmpty.....................

View 9 Replies View Related

How To Use Form Control Dropdowns To List Items Based On Condition

Jun 16, 2014

I have 2 dropdown lists.

The 1st shows the portfolio list and the 2nd one should display project names based on the portfolio selection in Dropdown1.

My data resides in another sheet where Column B is the Portfolio list,Column C project list and Column D to X some data related to the project.

I have to give cell link reference to the project selected in dropdown 2 so that the other values in the dashboard changes based on a vlookup formula.

I have attached the sample sheet for reference with some dump values.

Dashboard sample.xlsx

View 3 Replies View Related

Avoid Hard Coding Control Name Inside Control Event Procedure?

Mar 4, 2014

Is there any way in VBA to refer to a control in its own event procedure without referring to it by name/hard-coding?

It might be clearer to explain by a dummy code example:

[Code] ......

I'm seeking what I would need to replace Line1 with.

View 11 Replies View Related

Data Validation - Control What User Enters Into Single Cell Based On Others

Nov 29, 2013

I want to control what a user enters into a single cell based on others.E.g.

A B
1 20 500
2 50 500
3 75 500
4 100 300

Column A is a thickness and column b is a width what I want to happen if the user enters a thickness in cell D1 and a width in cell D2 I want an error to pop up if for instance the user enters 100 as a thickness to only allow a maximum of 300 in the width. likewise if the user enters a thickness less than 100 in cell D1 to allow him to enter up to 500.

View 4 Replies View Related

Select Next Control / Cell On Worksheet After Enter In Control

Jan 9, 2008

Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?

View 9 Replies View Related

Copy Data From One Sheet To Another Automatically Based On Number Selected In Dropdown Control

Apr 25, 2013

I'm trying to copy data from one sheet to another sheet automatically based on a number selected in a drop down control.

I have used OFFSET to pull some information successfully from a list. This is easy for me to do with single entries and a list and has worked well. I want to do similar but with a dataset not just single cells.

But, I can't seem to make OFFSET work to show a set of data easily.

Perhaps I will need VB code to do so? Or there is another control I'm not aware of? I've done very little with VB.

I'll attach a sample file to try to show better what I'm trying to do.

View 7 Replies View Related







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