Evaluate UserForm Control Where Name Is In 1 Cell & Property In Another
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Set Control Visibility Based On Tag Property
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 Replies!
View Related
Set Variable To Forms Control & Get Value Property
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 Replies!
View Related
Macro That Deletes Sheet With Control & Shows UserForm Causes UserForm To Disappear
This is weird - if you delete a sheet that contained a control then a. showing a modeless userform resluts in a userofrm that goes invisible at subroutine End b. public variables lose their value These things do not happen if the sheet did not contain a control. Attached is an example file - put the inputfile.xls in your default file location (or add a path in the code) then open the ProblemDemo.xls and run the main macro to see it fal - isthis another Excelbug I've found?
View Replies!
View Related
Show Userform - Invalid Property Value Error
I created a form that my employees could use to log their work. When I toggle between the combobox and the qtytextbox I get an "invalid property value" error that debugs to the line of code that opens the userform. I have no idea how to fix this. The spreadsheet has a button that shows the userform. This code is: Private Sub rectangle1_click() If frmLCWork.Visible = False Then frmLCWork.Show Exit Sub End Sub The userform has a combobox for employees to select the completed "task", a quantity textbox and then an "add to log" button. The form code is: Option Explicit Private Sub cmdAdd_Click() Dim sStep As String Dim Row As Long 'check for a task If Trim(Me.cbxTask.Value) = "" Then Me.cbxTask.SetFocus MsgBox "Please specify which task you completed." Exit Sub End If....................
View Replies!
View Related
Evaluate One Cell Against An Array
I have two sets of information, on one hand I have telephone numbers and in the other set I have prefixes and countries. My goal is to tell to which country each number belongs: i.e. Numbers Prefix Country 4476324125 44 UK 3354326544 33 France 9713425432 971 UAE 9143253245 91 India 1343543253 1 USA 4432412412 4924241214 7431242424 So I would need to add a column next to "Numbers" saying to which country each number belongs. My list has a few thousand numbers and a couple of hundred prefixes. I tried with some array formula, but cannot make it work.
View Replies!
View Related
Evaluate Multiple Functions In The 1 Cell
When using watch window to watch a formula that has multiple formula's in it, is there a way to see the result of 1 formula within the big formula? E.g. say you have: = SUMIF(A1:A11,"207",B1:B11)/COUNTIF(A1:A11,"207") And you put a watch on that, but in watch window it shows the result of the whole formula, say if you want to watch only the SUMIF part? Im using Excel 2003 by the way.
View Replies!
View Related
Toolbar Control On Userform ..
I've got the bare bones of a toolbox control on my userform but I can seem to find anyway to refer to the buttons and control them (I’ve tried using the properties page but it doesn't seem to give me the options I need). I want to be able to add a macro and a picture to the buttons, is this possible? Eg. With Userform1.Toolbar1.button("x") .image/picture/faceid = “x” .action = "macro1" end with I've looked for ages on the board but can only find threads on creating toolbars in the application, not on userforms.
View Replies!
View Related
Cursor Control With Userform
I have created a simpel userform that shows sales total and it is activated [.show function] whenever an entry is made in the order column. All of this works fine. The only problem I have is that the curser jumps in to the text box and doesn't return to the order column where next entry needs to be made. How do you move the curser out of the userform, back to the activesheet? Ideally it should move to the next cell for the user to make entry. I am attaching my sample file here. It has some odd things that I was just playing around with as learning experience. But the main question is how to control the curser.
View Replies!
View Related
Filling Control On Another UserForm
I am trying to add a list of items to a combobox on form1 from form2 where form 1 has a variable name. I can use: x = UserForms(0).Name VBA.UserForms.Add(x).show to show a userform with a variable name but I can't get it to add anything to the combobox using this VBA.UserForms(x).Combobox1.Additem
View Replies!
View Related
Userform Control Blink
This code works fine on a normal userform But I declared the userform with "New" in my calling procedure myForm As New form How can I get it to work?? Or more interesting, why is it not working Also what exactly is the advantage of using the keyword New I have propertys set in the userform, but other than that.. Can someone tell me if the load function in this case is better or not Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Private Sub cmd_click Call blink End Sub Public Sub Blink() Dim i As Long For i = 1 To 20 ' set color Form.cmd.BackColor = &HFFFFFF DoEvents Call Sleep(60) Form.cmd.BackColor = &HFF& .....................
View Replies!
View Related
UserForm Control Looping
Split off from Loop Through ActiveX TextBox Controls On Worksheet. Here is a zip file with the files in it. The main one that I am working on now is PO Form.xls. I want to start with the Qty column and copy any values that are entered into the form to the file called PO.xls in the Qty column of this file.
View Replies!
View Related
UserForm Control TextBox Or ComboBox
If this control is a TextBox, I would like to read the Text property, and if it's a ComboBox, I would like to read the Value property. Public Function readValue(c As Control) As String If (TypeName(c) = "TextBox") Then ' convert the Control to TextBox then put readValue = c.Text Else If (TypeName(c) = "ComboBox") Then 'convert the Control to ComboBox then put readValue = c.Value End If End If End Function
View Replies!
View Related
Programmatically Remove A Control From Userform
Suppose I have a userform named myUserForm and a checkbox on it named myCheckBox. How on earth do I remove myCheckBox from myUserForm at run time? I've tried every which way I can think and I always get "error 444". Some things I've tried are: myUserForm.Controls.Remove "myCheckBox" myUserForm.Controls.Remove(myCheckBox) myUserForm.Controls("myCheckBox").Remove myUserForm.Controls.RemoveControls "myCheckBox" I've put this in a standard module, the ThisWorkbook module, even in the myUserForm code module and each time it's the same error.
View Replies!
View Related
Setfocus From Module To Userform Control
I have a project with many textboxes on different forms. On some of the textbox_exits on some of the forms, I call a standard module that checks what the user has input. If the user needs to change the input, a msgbox appears to inform the user the info needs to be changed. I have tried to use ControlSource, Name etc., But I cannot seem to setfocus back onto which ever textbox the input needs to be changed. I think it has something to do wih the _exit event, but not sure.
View Replies!
View Related
UserForm Control Not Showing Fully
I have a workbook which when opened connects to an external datasource (excel file) and downloads a table of information. While this is happening I have a userform which shows which has a lable saying (connecting to datasource, please wait....). However, when I open the workbook, the userform shows but the labels do not show, the userform just appears white.
View Replies!
View Related
To Set Focus To The RefEdit Control In Userform
I am trying to implement a simple userform using the RefEdit control. So I have the RefEdit control and an Ok (which has code attached to it) and a Cancel commandbutton. For some reason, I can't get the focus on the RefEdit control (i.e. when I activate the form, I have to actually click in the RefEdit box before it gets the cursor). Which property sets the focus in this control? Right now I have the Ok button Default property set to True. I have a commandbutton on the spreadsheet that activates the userform.
View Replies!
View Related
Userform Call Macro On Control Change
Is there a way to call a subroutine anytime any control is changed within a userform? For example, I have a userform that needs to go through and add/multiply almost every value in every textbox and also add together values associated with checkboxs. I need this to happen anytime a textbox value is changed or a checkbox is checked.
View Replies!
View Related
Enter Key On UserForm To Next Control Not Working
I have a User Form that is used to collect data. CommandButton1 loads the User Form data into the worksheet and CommandButton2 clears all data from the Form in preparation for entering the next record. Immediately after a user first opens the Form, the very first time CommandButton1 is clicked, the Enter Key stops working. At this point data can be typed into any TextBox on the Form, but when the Enter Key is pressed the cursor remains in the TextBox. (The Enter Key will not move the focus to the next Textbox in the Tab Order. Also, if I alter the code to set the focus on a CommandButton as the active control instead of a TextBox, pressing Enter on the active CommandButton does not execute the CommandButton's macro... the same behavior as a TextBox; nothing at all happens when the Enter Key is pressed even though the CommandButton has focus). At the point when the Enter Key stops functioning, if the user presses 'Alt-Tab' to leave the Form and then immediately uses 'Alt-Tab' to return back into the Form the Enter Key suddenly works again and continues to work correctly from that point on even after CommandButton1 is clicked. Again, the Enter Key stops working ONLY the first time CommandButton1 is run immediately following initially opening the workbook and Alt-Tabing into the Form immediately corrects it. I saw one other post in this forum with this same problem in 2003, but unfortunately it did not get answered.
View Replies!
View Related
Pass UserForm Control Values To Sub Procedures
I have been trying to use a multi-select listbox as the argument for a subroutine. For some reason, I keep getting a run time error, type mismatch. I'm baffled because when I define the specific listbox, it works fine, but when I attempt to pass the listbox I get an error. Public Sub OtherDirectTotal(Expense As ComboBox, CostCat As ComboBox, _ Cost As TextBox, Years As Msforms.listbox) Dim IDC As Double Dim IDCt As Double IDC = Val(UserForm2.txtIDC) / 100 If Years.Selected(0) = True Then If Expense = "Sponsored" Then If UserForm2.ChkODC = False Then Select Case CostCat......................
View Replies!
View Related
Collapsible Multi-Select UserForm Control
I would like to add a combobox type control that will allow multiple selections. I know that the combobox in the MS Forms Object Library only allows single selections and that the list box is the way to go to allow multiple selections, but I am trying to keep the form as small as possible. I would like to add a control similar to the one used on pivot charts, where the user clicks the dropdown box and can select multiple items. I'm hoping this control is installed with MS Office and just needs a reference to it, but I am open to installing third party controls as well.
View Replies!
View Related
Control Toolbox Appearing On Multipage Userform
I've started experiencing a strange problem. The control toolbox keeps appearing when the first page on a multipage userform is activated. I can't figure out what's causing this, and after searching the forum, with no luck, I thought I would see if anybody else is experiencing the same problem, and how to fix it.
View Replies!
View Related
Control Toolbox Pops-Up When UserForm Run
i've gotta demo an Excel app in a few days and this never really bothered me, but someone recently pointed out that it can get distracting for the Control Toolbox to pop in and out randomly. the form is being built for use by other staff, and i suppose it would also be distracting for them if the toolbox arbitrarily appeared here and there. is there a way to turn it off?
View Replies!
View Related
Place Code Behind Userform: Active Control Colored
I have created a user form and I am trying to get the active control colored. I am using the code from Mr Excel's VBA book - starts on page 454, and I have entered all the code, but now when I activate the user form I get an error ....."Compile Error. Invalid attribute in Sub or Function." and when I click OK, it takes me to this line of code..... Private WithEvents objForm As clsCtlColor In the book, this is where I am supposed to start entering the code "behind the userform" rather than in the class module. So, I assume this means that this code goes with all the other code for the user form (in VBA project click on form, then view code). Am I wrong? Should I be adding a module? Not sure what I am doing wrong. Here is the code I have in the class module.... Public Event GetFocus() Public Event LostFocus(ByVal strCtrl As String) Private strPreCtr As String Public Sub CheckActiveCtrl(objForm As MSForms.UserForm).......
View Replies!
View Related
Move To Next UserForm Control With Down Arrow Key Press
I am trying to setup a shortcut key while in a userform so you can press the page down key and the focus will jump to an "Ok" or "Close" button. I tried the following keypress procedure without any luck. Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyPageDown Then Me.btnClose.SetFocus End If End Sub I even tried a similar test on a textbox control wihtout luck. Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyPageDown Then MsgBox "You Pressed PageDown Key" End If End Sub I should say that this is a form that has a multi-page control on it and the showmodal property set to false. It's almost like the event doesn't even fire. Does anyone have any ideas on how to get this to work?
View Replies!
View Related
Load Worksheet Picture Into UserForm Image Control
I have an image box on a userform in Excel VB. Is there ANY WAY to load an image into this image box from an object that I have loaded into an excel worksheet something like If userform1.checkbox1=True Then userform1.image.picture = loadpicture (Worksheets("Sheet1").shapes("Object 1")) ElseIf userform1.checkbox2=True Then userform1.image.picture = loadpicture (Worksheets("Sheet1").shapes("Object 2")) Else msgbox "No image" Endif
View Replies!
View Related
Pass Calendar Control Date To Selected TextBox On Another UserForm
I have a workbook that has multiple spreadsheets. Data is added to the spreadsheets using userforms for each spreadsheet. I have created a pop-up calendar to add dates and want to know if there is a way to add dates without having to create multiple calendars for each txt field on each userform. I would like it so that when I click on the calendar it would put the date into the text field it was launched from. This is the code in the calendar which only puts the date into the field named Dat_Clsd. Private Sub CMD_Close_Click() .....
View Replies!
View Related
ComboBox Error "Could Not Get The List Property - Invalid Property Array Index" When Typing Out Of Range
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 Replies!
View Related
Access Control Properties Of Controls Within UserForm, MultiPage & Frame
I want to access the Properties of a number of controls in a running form, and these controls may or may not be contained in a Frame or a MultiPage. In particular I want the Top and Left for these controls, which means I have to first find out if the control is contained in a Frame or MultiPage so I can get the reference for Top and Left. I'm ok with doing this for controls inside a Frame, but the MultiPage is eluding me. I get an error when I try to access these controls and it looks like they are actually owned by the individual Pages of the MultiPage. how do I find out if a given control is contained in a given MultiPage? Validate UserForm MultiPage and Frame Controls
View Replies!
View Related
Set Text & Hide Control On UserForm Show/Load
I'm trying to do is set up a form load event to initlize some controls. Here is my Private Sub Form_Load() 'Initialize the form lblProcessing.Visible = False txtFileName.Text = "Enter a file name" End Sub At the moment, this event is not triggering. I have the code in the code behind my form - should it be in a module? PS: This site is great - it's answered a lot of my other questions so far without me having to make any posts.
View Replies!
View Related
Range Text Property To Add String To Cell
I don't understand what I am missing. but I can't paste a string into a cell [range.cells(x,y).text = string] Here is my ' ' -delete any rows that contain "Fab 11" ' -rename any cell that has "Supply x" (Delete " supply x" from text) Dim cellText As String Dim i As Integer Dim lastRow As Long Dim rngSigac As Range Dim wb As Workbook Dim ws As Worksheet
View Replies!
View Related
Change Visible Property Of Check Box Based On The Value Of A Cell.
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 Replies!
View Related
Userform To Control Macro Features. Macro = Search For File Type
I have a macro that I found somewhere on the net to look within a folder and list all the files of a certain file extension. The macro to do this is in the attached example and is called 'Get_File_Names_Within_Dir_ext'. I have created a basic userform outline, 'UF1' for the user to define: Select File Extension Select Folder to Search Destination Sheet I just don't have any idea how to sync the two. If you type 'exe' into 'TB1_File_Extension' of 'UF1' the macro should search for '*.exe' files within the specified folder. The search folder 'RefEdit1' box should open a windows explorer box (or some such) so that the user can select the directory in which to search for the previously specified file extension. 'TB2_Destination_Sheet' is a text box for the user to type the sheet within the workbook in which to list the files found within the specified directory. 'CB1_Find_Files' should activate the macro to find any files for the specified criteria. There is also a Button 'Find File Types' in Sheet1 of the file which should activate the userform 'UF1'.
View Replies!
View Related
UserForm Navigation Bar Control Error "1004"
I am having some difficulty with user form navigation bar - when scrolling to the end of the data it works just fine. When scrolling towards the beginning of the data it breaks with a "Runtime 1004: Application-defined or object-defined error". The reason may be that the beginning of the data is not correctly defined. The data has a header row. Code that I think is relevant to the navigation bar follows: Private Sub Navigator_Change() 'When Scrollbar value changes, save current record and load 'record number corresponding to scroll bar value 'Call SaveRecord Set RangeData = Range("CONTACT").Rows(Navigator.Value) Call LoadRecord End Sub Private Sub UserForm_Initialize() 'Load 1st record in CUSTOMER and initialize scroll bar With Range("CONTACT") Set RangeData = .Rows(2) Call LoadRecord Navigator.Value = 2 Navigator.Max = .Rows.Count End With End Sub...........................
View Replies!
View Related
Specialcells Property Error "Unable To Get The SpecialCells Property Of The Range Class"
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 Replies!
View Related
Evaluate Each Row And If The Last Value For That Row Is X, Then Highlight That Cell
I want to do it evaluate each row and if the last value for that row is X, then highlight that cell and the Server name for that row. I have used conditional formatting but it seems that I cannot specify an option to look at the last cell in a row, evaluate that cell's value and then apply the formatting. I can only have conditional formatting highlight every cell in that row that matches the condition. I only want the last cell, which represents the most current data, to be highlighted, as well as the category name, if the condition is met.
View Replies!
View Related
Unable To Set ColorIndex Property Of Interior Class: Fill Cell Color Macro
I have an error message that says: Run time error '1004': Unable to set the colorIndex property of the interior class. I attached code for your reference. If (Range("B10").Value = "Gift" Or Range("B10").Value = "Entertainment") And Range("C10").Value = "" Then Range("C10").Interior.ColorIndex = 6 MsgBox "Please Fill in the Person's Name & Company." Range("C10").Select Range("C10").Interior.ColorIndex = 6 End If
View Replies!
View Related
Drag From Treeview Control To Spreadsheet Control
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 Replies!
View Related
Determine Active Control On Multipage Control
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 Replies!
View Related
|
TRACKER
