I have somecode that scan column "A", and add dynamically "TextBox" Control according to some condition.
How to add event on dynamically created controls?
Private Sub btnSubmit_Click()
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
For Each rng In Selection
I have a userform with a single blank multipage. At runtime additional pages are added, the number of pages depends on input from another userform. Six frames, containing labels, textboxes and comboboxes are then added to each page.
I need to be able to use the textbox and combobox change events of these dynamically created controls to perform lookups and calculations. Although I can name the controls at the time they are created, it is not feasible to write code specifically for each control (I can have over 1,600 text boxes distributed over 9 pages, for example).
Having searched for some time on how to achieve this I believe using a class module is the way to go. However, how to use a class module is just not sinking in I'm afraid.
HTML Private WithEvents mpTextBox As MSForms.TextBox
Private WithEvents mpComboBox As MSForms.ComboBox
Private Sub Class_Initialize()
Set mpTextBox = MSForms.TextBox
Set mpComboBox = MSForms.ComboBox
End Sub
Private Sub mpComboBox_Change()
MsgBox "ComboBox value has been changed."
End Sub...........................................
I have a userform that has one combobox at the top created manually. When the userform is opened, the user select an option in the combobox (these options are taken from a range on 1 worksheet). From the selection of the combobox, I use the comboxbox's change event to create and display 5 columns of textboxes and 2 columns of command buttons on the userform.
The number of rows of textboxes created depend on the option selected from the combobox since each option links to a different range of cells. Each of the 5 textboxes in each are set to be ".enabled = False" and display text as per the cell values within a range on another worksheet. 2 Columns of command buttons are created at the end of each row of textboxes - 1 is enabled and the other is not.
The creation of the textboxes and command buttons works as required. However, I am having problems with setting click events for each command buttons. When the 1st column of Command buttons are created, I need the click events to be created and filled out with 2 actions:
1. Enable all textboxes in the same row as the command button
2. Enable the other command button in the same row.
Here is the code I have so far that creates the textboxes and command buttons.
Each of the 5 textboxes and 2 command buttons have a unique name so the 1st row will have textbox and command button names of cTxtA1, cTxtB1, cTxtC1, cTxtD1, cTxtE1, CmdAmend1 and CmdConfirm1. The 2nd row will have the same names but with 2 on the end and so on. The bold sections is the code for the creation of the command buttons that I want click events for.
Code:
Private Sub CboTeamSelect_Change()
Application.ScreenUpdating = False
If CboGroupSelect.Value = "" Then Exit Sub
Dim cTxtA As Control, cTxtB As Control, cTxtC As Control, cTxtD As Control, cTxtE As Control
Dim CmdAmend As Control, CmdConfirm As Control
Dim iNum As Integer
Dim TxtTop As Long
[code]....
Is it possible to dynamically add or remove controls on an Excel userform using VBA? Doing this before the userform is displayed would be great.
View 9 Replies View RelatedI am having a excel vba code that copies tab from different excel file and then i want to use in sql query. It is always giving me error saying that "The Microsoft Access database engine could not find the object 'rahul'. Make sure the object exists and that you spell its name and the path name correctly. If 'rahul' is not a local object, check your network connection or contact the server administrator."
Though, when i rerun the macro after reopening the excel file, it is working fine. Seems like tabs dynamically created cant be used. Is their any way to get it done at runtime? I want to delete new created tab once data from new tab is inserted to required tab.
my macro calls first copyAllSheets(filename) and then writeSheet().
Code:
Private Function copyAllSheets(ByVal fname)
Dim destSheetCount As Integer
Const FUNCNAME = "copyAllSheets"
On Error GoTo ErrorHandler
Set wb = Workbooks.Open(fname)
destSheetCount = ThisWorkbook.Worksheets.Count + 1
wb.Worksheets("Buyer").Copy after:=ThisWorkbook.Sheets(1)
ThisWorkbook.Worksheets(destSheetCount).Name = "impBuyer"
[code]........
This is part of code so probably it wont be easy to understand the flow. but mainly i want to know how can i have newly created tab as table_name.
Code to create buttons on a userform. I have adapted his code to do what I want. The code he gave me was:
[Code]....
In the Class Modules:
[Code] ........
All buttons when created are coloured yellow. When a button is clicked, it turns blue (to indicate that the button was clicked. However, my problem is that when another button is clicked, it also turns blue as coded. This means that both are showing blue colour. I want all other buttons to turn yellow and only the button clicked should turn blue.
The following code dynamically creates a number of textboxes within a frame with a label right next to it. There are two classes.
[Code] ......
The two classes look like this:
[Code] .....
This works just fine to create the dynamic series of textboxes and corrosponding labels. I am having trouble with the reference between them. for example (within the class module):
[Code] .....
As shown, the reference to the corresponding lable from within the class doesn't work. How to reference the correct corresponding label.
Basically, I am taking user input to determine how many textboxes to create during runtime, and want to call the contents of those textboxes after the user has put something in them.
View 1 Replies View RelatedI have 100s of name ranges in my spreadsheet. I am using a series of drop down boxes for the user to select the info that they want to see. Based on the dropdown box, the macro "dynamically generates" a range name from which to pull the info.
For instance, if they Select Manager = Terry, Analyst 1, and Month = Jan, then the range name would be JanTA1
The if statement would dynamically create this name by concatenating the info
If manager = Terry
mnger = "TA"
if analyst = 1
anlyst = 1
if month = Jan
mnth = "Jan"
rangename = mnth & mnger & anlyst
So, I am dynamically creating the range name based on user selections...then to display it, I want to set the display cell = to the value
Range("display cell").Value = Range(rangename).Value
The problem is, I am getting an object error every time I try to refer to a range this way.
If I type:
Range("display cell").Value = Range("JanTA1").Value, it works
For a form that dynamically creates a series of text boxes and labels, does anyone know how to change the caption of a dynamically created label to display the value of a cell in a worksheet? The cell in the worksheet changes according to input on the text boxes. In the form's code, I have:
Dim newLblFreq As MSForms.Label
Set newLblFreq = Me.Controls.Add("Forms.Label.1", "lblfreq" & i, True)
newLblFreq.Caption = Range("V" & i).Text & " Hz"
But once the label is created, the caption won't change, even if Range("V" & i) changes...............
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?
I am working on a dynamically produced userform but the dynamically created Cancel and OK buttons don't work. I have been able to get round it by adding the buttons to the top of the form (non- dynamic) but I'd really like to be able to create them dynamically based on variable for other parts of the project. How can I produce dynamic command buttons that actually work?
Here's my code but I'd be happy with generic code I could use.
Private Sub UserForm_Activate()
' Define variables
Dim LabelV As MSForms.Label
Dim CheckboxV As MSForms.Checkbox
Dim rngFields As Excel.Range
Dim field As Excel.Range
Dim lngNextTop As Long
Dim lngTitleBarHeight As Long
Dim ColumnNum As Integer
' Set constants
' Choose height and width of Label box
Const cTextBoxHeight As Long = 12..............
I am having trouble working through the examples given on other posts for this topic. I have a userform that creates a number of controls at runtime. One group of controls is a series of textboxes and a label underneath that sums the values of the textboxes. When a user changes the value in a textbox the label needs to update the sum. I have read about created class modules to define the event but it is all going over my head.
A piece of the code that creates the controls is:
I am trying to dynamically add controls to my user form based on some values in my cell. I am successfully able to create a text box dynamically but my label is not getting displayed. here is my code
Private Sub UserForm_Activate()
On Error Resume Next
If (ThisWorkbook. Sheets("Sheet2").Cells(1, 8) <> "FALSE") Then
Dim ctl As Control
Dim ctl1 As Control
Set ctl1 = Me.Controls.Add("Forms.Label.1", ctl1, True)
With ctl1
I have a code that adds a couple of ComboBoxes to a UserForm (the number of ComboBoxes is variable). Now that I want to control a certain ComboBox based on the selected value of another ComboBox (change or click event), how do I do that via VBA code?
View 3 Replies View RelatedI am looking for a macro that will (most likely via button) create new table under an already existing one.
So for simple example, I have this ....
I have created a menu called "Temp" and a sub menu called "Temp1". When the user click on Temp1, I need to dynamically add some menu items under Temp1. Is this possible in Excel?
I need to do this on the right click menu. for example, along with "Cut", paste" etc menu's I need to add a Menu Item, Temp and under this menu, I need to add a SubMenu, Temp1. When the user click on the Temp1, I need to populate the child items.
I've managed to get coding that allows me to insert single rows dynamically based on a cell value. Example is if I enter a value in cell C3 the coding inserts the same number of rows below.
I've also created a simple macro that inserts a multiple row block under the active cell, complete with formatting, text and some merged cells. Example is I run the macro and it will insert a block of 3 rows, complete with formatting, borders, text and merged cells.
My challenge is to bring these two elements together. So enter a number in a single cell which will then insert the same number of row blocks, complete with formatting etc. I've added a file to sample what it is I'm trying to achieve. I can add the coding I already have as well.
Dynamic list sample.xlsx
I have problems with dynamically adding and removing checkboxes. I have a form and there is a button to add more rows to the table. Every row includes a checkbox too.
So first i add a row, and then add a checkbox to a specified cell in that row.There is a button for removing rows as well (witch should removes checkboxes as well).
My problem is when i ad the checkbox the linked cell property only works for the first one.When i add the second row the linked cell of the firstly added checkbox changes to the one in the new row and the new checkbox has no linked cell. I am adding the stuffs as follows:
VB:
Private Sub addBtn_Click()
Dim y As Integer
y = findFunc("end") // Y define where To insert the New row
Cells(y, 11).EntireRow.Insert
Cells(8, 11).Copy
[Code] .....
Any way to add checkboxes dynamically.
I have a userform with a large number of textboxes. Say 100. I want to clear them all after the user submits the data to the spreadsheet. Essentially resetting the userform.
Currently I have:
userform1.textbox1.value = ""
userform1.textbox2.value = ""
...
userform1.textbox100.value = ""
Is there a way to change the textbox number to a variable? Something like:
For textboxN = 1 to textboxN =100 Do
textboxN.value = ""
Loop
Excel 2007
I have a few dozen pictures created when a macro runs. They all have unique names. I'd like to add comments to cells, where the cell.value decides which picture to pull. All the examples I've found online show how to do this if you have pictures saved on your hard drive by referencing the file path "c://mydocs/...blahblah/"
Is there a way to reference the pictures I've created/named with my macro?
Here's the snippet of code that creates the pictures and names them:
Code:
For i = 2 To Application.CountA(Sheets("Allocation").Rows(1))
Sheets("Allocation").Activate
Set rInput = Sheets("Allocation").Range(Cells(1, i), Cells(10, i))
sPicName = "_" & Sheets("Allocation").Cells(1, i) & "_"
sSheet = Sheets("Allocation").Cells(3, i)
dDate = Sheets("Allocation").Cells(5, i)
[Code] ......
Here are some examples that are close to what I'm looking for.
VBA Popup Pictures - 1108 - Learn Excel from MrExcel Podcast - YouTube
VBA Express : Excel - Add pictures that float like comments.
I have a worksheet with lots of things going on... the end user needs access to the Name field, DOB field, all the scroll and form control option buttons in the 9-question sheet, and the macro-enabled reset button at the bottom. Formulas are sprinkled throughout the worksheet, in columns T through AC... option button links go to AA and AB.
I would like to protect the entire sheet so none of the fields, except where indicated above, could be selected or edited... but when I protect the sheet, I get errors when trying to use you form controls, and the formulas do not respond. And finally, when protected, I get a debug error on the reset macro.
I have a UserForm with a Text Box, I populate that Text Box with a number (say 5) and then the following code runs:
Code:
Private Sub tbOverrideMokWh_Change()
Application.EnableEvents = False: Application.ScreenUpdating = False
With tbOverrideMokWh
[Code]....
After the Sub is run 1 time, it runs again. Why? I've disabled Events?
I am aware that I can use single changing events in worksheet change events. For instance, if column 1, or A is changed, do something. This is only a single If statement, i.e. either the condition is true, or not. What I am not sure is if I can use two changing events, i.e. two conditions. For e.g. I would like if Column A value is X and Column B is "Active", action it, but only if two conditions are true.
For.e,g. The below syntax does not work. If it is only column A, it does work, but I want both A and B to be true, then copy and paste the target does not anything.
VB:
If Target.Column = 1 Then
If Target.Column = 2 Then
If Not Intersect(Target, Range("A2:A" & Rows.Count)) Is Nothing Then
If Not Intersect(Target, Range("B2:B" & Rows.Count)) Is Nothing Then
If Target.Value = "X" And Target.Value = "Active" Then
I have an Index Sheet where I would be typing the name of the Sheet and a Command button to execute the operation of Adding the Sheet with the name mentioned in the Column C..
View 14 Replies View Relatedsome times i choice (Initialize , active, open, click
Workbook_BeforeSave)
and so on
tell me as the differences and usage of these strings in order to improve our(my) programming skills.
I am trying to write a code where user has to respond to input box option depending on yes or no selection. There are two types of responses to different questions. One needs to respond between 1 and 100 (if yes) and for some other questions needs between 1 and 4 (if yes). A "no" will enter zeo value. But it is not working. I am using two named ranges "VALIDCELLS" (for 1 to 100) and "FREQVALIDCELLSS" (for 1 to 4). Here is the code;
View 6 Replies View RelatedI have early made a macro that will change some of the cells before printing and then changing back again to initional state after the print out.
http://www.excelforum.com/excel-misc...-printing.html
But now I have to do let people also print a copy a regular way, without the macro I made before. But if they go through the regular way iof printing I would like to insert at header in red color, to notify them that this is just a preview and not the way to print. And here we come to the problem ....
why am I unable to create events on my worksheets? They were usually available to me when I selected "Worksheet" on the dropdown in vba.
When I open Excel, only "General" comes up in the drop down menu. What am I missing?
I've created an addin to reformat spreadsheets that I receive in a particular format. What I would like to do is enable events so that whenever a spreadsheet is opened the reformatting procedure is run (this also validates whether the spreadsheet is of the correct format).
I have created a class module with the following code (exactly as the Excel help):
Public WithEvents App As Application
Dim X As New EventClassModule
Sub InitializeApp()
Set X.App = Application
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
SortE1Output 'This is my procedure that determines whether the
'spreadsheet is of the correct format and then reformats it
End Sub
"After you run the InitializeApp procedure, the App object in the class module points to the Microsoft Excel Application object, and the event procedures in the class module will run when the events occur."
When I try to make a call to the InitializeApp procedure in the auto_open procedure (in a different non-class module) I get a "compile error: sub or function not defined".