Changing The Backcolour Of A Dynamically Added Control
Dec 1, 2007
I'm trying to give a visual indication to a user of whether a combobox has been correctly filled out.
Basically I want the back colour to be red if the value is "Not Used" and green if the value is anything else.
Normally this wouldn't be a problem, I'd just use the combobox's change event to trigger a check, and adjust the backcolour appropriately.
However, the combobox is dynamically added to the form (depending on options further up the form) - and the particular combobox may not always be created.
View 9 Replies
ADVERTISEMENT
Nov 20, 2013
I am using table driven forms controls on userforms. E.g.:
Excel 2010ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG1UI NameMultiPage ParentPage
ParentControlNameTopLeftHeightWidthCaptionTagControlTipTextSpecialEffectWordWrap
MultiLineBorderStyleBorderColorBackColorBackStyleForeColorColumnCountColumnWidthsListStyle
[Code] .....
I use the following (work in progress) function to add the controls to the userform (usually added to page or frame).
Code:
Public Function AddControls(ByVal objTarget As Object, ByVal strUiName As String)
Dim rngControls As Excel.Range, rngProperties As Excel.Range
Dim rngControl As Excel.Range, rngProperty As Excel.Range
Dim objControl As Object
With shtFormUI
Set rngControls = .Range("D2:D" & .Range("D" & .Rows.Count).End(xlUp).Row)
[Code] .....
Now I need a means of trapping the controls events. I thought I could use a class, e.g.:
cFormEvents
Code:
Option Explicit
Public WithEvents lblLabel As MSForms.Label
Public WithEvents tbxTextBox As MSForms.TextBox
Public WithEvents cbxComboBox As MSForms.ComboBox
Public WithEvents lbxListBox As MSForms.ListBox
Public WithEvents cbtCommandButton As MSForms.CommandButton
Private Sub lblLabel_Click()
[Code] .....
It seems I cannot reference the controls because I they are added at runtime. For the given example, I want to run whatever procedure name appears for lblPrimaryContact in column AC (click event). So in my userform module I instantiate the class, but I get an error when I try and reference the control:
Code:
Set m_clsFormEvents.lblLabel = Me.lblPrimaryContact
Error is "method or data member not found".
Any alternative method to grab the click event for the control added at runtime?
View 9 Replies
View Related
May 5, 2007
I am trying to find a way to dynamicly change CSV files .What I want is to add another column to the table with the values that based on the pre programmed parameters. For example if i have A,B,C,D columns and rows 1,2,3,4,5,6,7 I need macros to add values in column E based on the values of column B(for example)
I want to be able to pre-set those algorithms.For example for value 1033 in column B I need macros to insert value 1.25 in column E in the same row,for value 951 in column B I need 0.3 in column E in the same row. Is it doable just in Excel?
View 3 Replies
View Related
Jun 20, 2008
my file was too big to attach, so to get it, go to [url] (p: SOCIALCHARM [all caps]). It's the only file underneath the 'documents' section.
This is a complex sheet, so I won't go into everything I'm doing, but really just the part I need help with. Instead of trying to explain it abstractly, I'll use a a concrete example (you'll need to look at the sheet to understand this).
The general idea is that I results from an online ad campaign (ie I ran ad X on site Y in position Z), and - given the results I have - try to optimize which ads I run where. Each in the real data (row 167 and below) represents a 'space' where an add can be run.
First, row 551:
The original recommendation is in Column T, which is Brand "Buddies" (column V). The total # so far for brand "Buddies" (col W) is less than the limit, so Column X = 0, and the updated recommendation (Column Y) is just the original recommendation from Column T.
Now, row 552, (the condition I'm having trouble with)
The original recommendation is in Column T, which is Brand "Pink" (column V). The total # so far for brand "Pink" (col W), however, is now OVER the limit, so Column X = 1. This means that excel now needs to search all of the creatives (in column E), filter out all the ones that don't match the size of the current creative in question (ie that are not of size 300x250 - E551), ALSO filter out any creatives of brands that have already hit their limit (in this example, brand "2-in-1" has already hit it's limit, as you could check by looking at X537), THEN, from the remaining creatives (ie those of size 300x250 and not of brand pink OR of brand 2-in-1), select the creative with the highest value in Column AI).
View 3 Replies
View Related
Oct 26, 2011
My workbook simply has multiple columns of data which will be summarized visually in a tag cloud (I have the tag cloud part taken care of already creating either a jpg or png, I simply have to change the extension within a batch file to change between picture formats)
I need to have the tag cloud image display and update depending on which column the user would like to summarize (for now I have simplified the issue by only having one of the data columns in my workbook, and will worry about the user selection of the data column to be summarized later).
My Main Question:
Focusing on the image display and updating now, whether it be an ActiveX Image Control or any other way. I've been playing around manually before I try and code it and I can not get either a jpg or png file to attach to the ActiveX control (both of which cause an Invalid Picture Message).
The file name can stay the same if need be and for now I think/assume that'd be easiest (I have my Tag cloud generator just replacing the previously existing image file of the same name).
Would ActiveX control be the way to go for this dynamically changing image?
View 9 Replies
View Related
Oct 6, 2006
Last weeks I've had too few spare moments to answer questions, hope to pick that up soon. I have a strange problem now with some checkboxes on a multipage control.
Please see the attachment.
I want to check all the 5 boxes on a page if the box above is checked, and vice versa, uncheck them all if the box above is unchecked. I use code of Dave Hawley to loop through the checkboxes.
This doesn't work, but oddly enough, it works if I insert a MsgBox in the code.
View 6 Replies
View Related
Sep 9, 2012
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Code:
Sub populate()
Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer
Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
View 6 Replies
View Related
Jan 25, 2007
I have a bunch of Option Buttons in a worksheet whose properties i need to change by running a macro. For example, I created an OptionButton1 in a worksheet using control toolbox. Now I want to run a macro to change it's name, groupname, linkedcell and caption. I did this macro but it doesn't work:
Sub Label()
Dim Code1 As String
Dim Link1 As String
Dim Form1 As String
Dim GroupName1 As String
Dim Caption1 As String
Worksheets("Section1").Select
Form1 = "OptionButton1"
Code1 = "FButton1"
Link1 = "FLink1" 'Defined in worksheet
GroupName1 = "FGroup1"
Caption1 = "Choose Function 1"
With Worksheets("Section1").Form1
.Name = Code1
.LinkedCell = Link1
.GroupName = GroupName1
.Caption = Caption1
End With
End Sub
View 3 Replies
View Related
Jan 28, 2009
how to change the color of a command button created by using the control tool box
View 2 Replies
View Related
May 31, 2006
Im trying to create many option buttons on my sheet and then change the GroupName and Name properties but im having no luck in accessing the properties. Here is the code ive got at the moment, could someone tell me how to access these properties. Shown below is a small section of the code, if you require the full code i will happily provide it. This is my first post and im not sure if this code tag thing has been done properly:
n = 18
Call Section_Header
Range("D" & n - 1).Value = " Case"
For i = 1 To UBound(Name_Case, 1)
Range("D" & n + i).Value = Name_Case(i)
Range("M" & n + i).Value = Cost_Case(i)
ActiveSheet. OLEObjects.Add(ClassType:="Forms.OptionButton.1", _
Left:=628, Top:=(n - 1) * 15.75 + (1 * 6) + (i - 1) * 15.75, Width:=46.5, Height _
:=15.75).Select
Next i
View 2 Replies
View Related
Jun 27, 2013
We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.
Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.
The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.
The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.
We can't figure out why the macro takes longer to run when no changes have been made?
View 8 Replies
View Related
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
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
Jan 12, 2007
I have created a userform within VBA which has a TreeView Control and a Spreadsheet control on it.
I have populated the TreeView control with data and what I want to be able to do is to drag the nodes off the TreeView control to the spreadsheet control.
I can drag onto a normal worksheet but not onto the spreadsheet control (the no drop mouse pointer keeps showing).
View 4 Replies
View Related
Oct 4, 2007
How do I determine which control the user is currently modifying on a multipage form (either changing, enterying or exiting the specific control). when I use "userform1.activecontrol" i get "multipage1" as the control name but I need the actual control on the specific active multipage. (also the .TABINDEX is for the multipage regardless of the on-page control) I use a generic data-field change SUBroutine so need the control name (and the TABINDEX) to provide my SELECT CASE. (so every fieldname_CHANGE calls the same SUB [with no parameters])
View 3 Replies
View Related
Sep 16, 2009
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
View 2 Replies
View Related
Sep 9, 2007
Moving between different sheets changes the procedure window, which is a good thing, but can also can be error prone and annoying to search through several windows searching for the procedure or module currently working on. Is there a setting somewhere to prevent VBE from changing the current procedure window?
View 4 Replies
View Related
Mar 12, 2003
Has anyone out their ever seen an Calendar type of control totally built in an Excel vba UserForm?
My problem that I’ve tried to resolve for some time is utilizing some type of pop-up calendar to eliminate format issues in my published Excel forms. I have tried a number of calendar controls but all have to be registered on the local machine and this cannot be guarantied for every machine.
If someone could direct me to a vba UserForm that has this built in that might do the trick. Or is their another way to deal with this?
View 9 Replies
View Related
May 21, 2014
I have a link
='S:2014DomesticATL[ATL-BP Eval-2014-04.xls]Business Partner Evaluation'!F$13
Now i have to compile 3 years of data for 200 airports so changing the month and year for every airport would be a challenge. I can do Replace all and change the year and month but instead of that i was thinking if i could have the year and month on one row and connect the links to that cell so that whenever i drag the cell my link changes
im looking at
2014DomesticATL[ATL-BP Eval-",D2",.xls]Business Partner Evaluation'!F$13
where in D2 i will have 2014-04 . But this formula doesnt seem to work. Is there anyway i can do this ?
View 3 Replies
View Related
Jul 11, 2008
I have a list of about 3,000 names and places of work, in two columns. What I want is to have the place of work in the first column, and then all the employees along that row.
There must be a simple solution to this, but I have been banging my head on my desk all morning trying to work it out. All the places of work are sorted, if that makes any difference.
View 14 Replies
View Related
Dec 7, 2008
I have a cell range of L3:N3 on Sheet 1 and on Sheet 2 in A1 i have the value of N3 of Sheet 1.
Now, if I move L3:N3 to eg. R10:T10 I want the value A1 ( Sheet 2 ) to be updated to T10 ( Sheet 1 )
Right now, it wont update dynamically, value of A1 stays at N3...
View 14 Replies
View Related
Jul 13, 2009
I basically am trying to just reference another cell (from a pivot table) from another sheet. ='SHEETNAME'!G5
But I Need the G to change... I basically need
='SHEETNAME'! + E29 + 5 to equal
='SHEETNAME'!G5 assuming G is in E29 but am not sure how to do this.
The other option is to use =GETPIVOTDATA("Sum of Amount_19",'SHEETNAME'!$A$3,"Label","Title") but I need "Sum of Amount_19" to change based on another cell, lets say F29.
View 3 Replies
View Related
Aug 24, 2009
Is it possible to access other tabs on my spreedsheet dynamically? =COUNTIF(July!$F$4:$F$200,$H7) not dynamic
A1 = July. =COUNTIF(A1!$F$4:$F$200,$H7) dynamic. but how is this done in Excel?? Is it possible?
View 2 Replies
View Related
Nov 4, 2013
I'm pulling data into some graphs from multiple sheets. The data I'm after is in the same place in each of the sheets, and the sheet name is varying only in that they refer to different dates, such as "01-11-13", 02-11-13 etc...
How would I go about dynamically picking up the sheet name. and referring to it?
For example if I have a column with all the different sheet names in it:
Col AA:
31-10-13
01-11-13
04-11-13
etc!
View 11 Replies
View Related
Feb 12, 2014
I'm trying to dynamically set the print area for a given worksheet. I use the code below and it works perfectly when it's executed upon opening the tab; however, the worksheet has filters which may lengthen/shorten the required print area.
Is it possible to execute the code below when the user selects print rather than when opening the tab?
[Code] ......
View 4 Replies
View Related
Apr 20, 2009
In the attached sample workbook I am trying to add the vendor number down the column H for each PO. I have copied a few of the numbers into the column, (in red), to show the desired result.
Basically, I will have a report daily with a variable number of suppliers, and variable PO's for each supplier. I need to add the vendor # to each row.
View 3 Replies
View Related
Oct 21, 2009
I am having a problem with a VBA macro I am trying to write. All I want to do is check all rows in a worksheet and group rows from the last bold row to the next bold row.
I seem to have the conditional logic correct, however, instead of grouping rows separately in each loop, it keeps joining them together.
View 6 Replies
View Related
Nov 19, 2009
I'm am trying something that is still a little over my head...
Normally, I merge cells the easy way:
View 8 Replies
View Related
Dec 31, 2006
In my worksheet cell A1 represents a financial reporting period from 1 to 12. It also represents the number of columns I need to sum in order to arrive at the desired period totals in this income statement worksheet. How can I get Excel to automatically change my SUMPRODUCT function as the value in cell A1 is changed from 1 to 12?
Here is a sample cell formula:
=SUMPRODUCT(ISNUMBER(MATCH(GL1SHL.XLS!$A$2:$A$332, {"4620","5220"},0))*(GL1SHL.XLS!$CE$2:$CE$332)*(GL1SHL.XLS!$I$2:$T$332))
The above formula represents the totals for period 12 for General Ledger accounts 4620 and 5220.
GL1SHL.XLS! references the workbook that has the financial data exported from my G/L application.
$A$2:$A$332 is the column which contains the G/L account number to be matched.
$CE$2:$CE$332 references a cell that resolves to the value 1 or -1 so that expenses which are exported as positive values become negative.
$I$2:$T$332 represents the 12 columns and 332 rows that contain all the financial data necessary to calculate the income for period 12 with each row representing a different G/L account's 12 periods of financial data. I need a method to dynamically replace the 'T' in $I$2:$T$332 and resolve it based on the value in cell A1. So if A1=1 then 'T' becomes 'I' and if A1=2 then 'T' becomes 'J' etc.
I would like to do this as a macro substitution instead of having to make 12 worksheets, one for each period, or instead of having 12 nested loops.
View 12 Replies
View Related
Dec 17, 2012
Is it possible to run a macro that will delete the LAST X rows? It must be dynamic, and now be fixed to a specific row.
View 8 Replies
View Related
