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
I 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.
I 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
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
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 create new sheet on button click event using this code
Worksheets.Add before:=Sheets(Sheets.Count)
ActiveSheet.Range("A1").Value = TextBox1.Value
but now i want that when this new sheet will open there will be some labels, textboxes and buttons n etc will be appear on that sheet so how can i display these elements on new sheet dynamically ??
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]....
I have about 5 text boxes. The user enters data in each text box and, as of right now, the data automatically gets saved as I am using the _change event. (TextBox1_Change()) This stores the value of each text box into a cell of my choosing on an Excel spreadsheet automatically.
From what I gather (according to a great ozgrid administrator ), it would be better, as a coder, to not use the _Exit event and instead, perhaps I should use the _Enter event. (TextBox1_Enter.) This way, when the user fills out the information in the text boxes, the information will NOT be loaded onto an Excel spreadsheet automatically as they type. (Which is what it does now by using the following code for each text box
Private Sub TextBox1_Change()
Sheets("Sheet1").Range("A" & intRow.) = TextBox1.Value........
I do have a data set from A1:C8 as follows:-
Brand Name(A1)Sales Value(B1)Margin(C1)Levi's100002000Arrow150002500Allen Solly120001300Hanes80001000John Miller250004500Disney280003200Ray Ban180002100
The above brands are categorized as follows:-
Brand NameProposed CategoryLevi'sConsignmentArrowBrandAllen SollyBrandHanesBrandJohn MillerPrivate LabelDisneyBrandRay BanConcessionaire
I would like to put formula from D1:D8 in such a way so that the Proposed categories are extracted automatically through formulae. This will help in getting the contribution calculated from different categories.
Brand Name(A1)Sales Value(B1)Margin(C1)Category(Desired Result)(D1)Levi's100002000ConsignmentArrow150002500BrandAllen Solly120001300BrandHanes80001000BrandJohn Miller250004500Private LabelDisney280003200BrandRay Ban180002100Concessionaire
I attach an example worksheet with the code I have thus far.
In my workbook I have other sheets, one of which changes and updates a specific one each time a new client's data is entered on said other sheet. Because I want to save the specific client's data and not lose it when another client's stuff is entered on this other sheet, I copy the sheet where the data is summarised (I called this sheet "Sheet to Copy From") to a newly inserted sheet and use Paste Special, Values Only to change all functions /f ormulae / Links ect to values.
I then change the name of the sheet to the name of the client.
I then use this sheet name / cell value to polulate a range on another sheet (Next Empty Cell) as a Hyperlink to the newly created sheet above. This sheet I called "Table of Contents".
how to code the hyperlink. Using Macro Recorder uses the specific case's names, but the Tab name to be used as Hyperlink value will always be the name of a new client,
i have 2 userforms one with textboxes and the other one with listbox and textboxes.
Everytime user input their data(ie:first name, last name, address etc) in the first form the data's going to be saved in Worksheet("customerSheet") and later on to be displayed in the second form. using the listbox you can select the customer's name and the customer info will be displayed in the textboxes.
here's my code in second form
Private Sub UserForm_Initialize()
Worksheets("CustomerSheet").Activate
Range("A1").Select
'ActiveCell.CurrentRegion.Name = "Database"
'Selection.End(xlDown).Select
'ActiveCell.address(False, False)
viewCustomerBox.RowSource = "A2:A15"
End Sub
I am using VBA to create a word document (.docx). This word document contains plain text content controls as well as picture content controls. I then use VBA to automatically select a picture based on the code below
Code:
Set oCC = Word.ActiveDocument.SelectContentControlsByTitle("TabPic").Item(1)
On Error GoTo TabErrorHandler
oCC.Range.InlineShapes.AddPicture Filename:="X:XFERANDREW-TDCD " & LblVL &
[Code].....
After the document has been closed down I try to open it again and I am told "The file cannot be opened because there are problems with the contents."
When I click details it says "Unspecified error" and "Location: Part: /word/document.xml, Line: 2, Column: 0"
If I click ok it says "Word found unreadable content in "". Do you want to recover the contents of this document? If you turst the source of this document, click Yes.
Clicking Yes opens the document with all the contents and it is now renamed to Document 1. If I click no it does not open.
I use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell
Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?
how to combine the content of 2 cells into one cell and have the information separated by a comma.
For example:
CELL 1:
Software 1
CELL 2:
Spreadsheet Software
The desired results is:
CELL 3:
Software 1, Spreadsheet Software
Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.
Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"
I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
i have 5040 rows in a sheet. in columns A,B,C,D,E,F, i have some input. and based on that i have some formula on column G.
lets say, min value is at row 4094 by looking at G column... i want to call entire row to the, lets say K L M N O P ....i need a formula for that....
random numbers are included in the formula. So ever time i make any changes on the sheet, min value changes even thought i dont play with the formula and input... so i want my new min value shown at row 1 of K L M N O P, every time i do that
how can i do that?
I am creating a set of lists, each on a different sheet. I want to have the first sheet as a sort of "Title Page" with basic information from the others. Is it possible to create a link of some types so that I could click on "Magazines" for example and go to the sheet called Magazines, or "Books" and go to the sheet called books. The latter would be very handy as this workbook has around 30 sheets in it, which is a pain to look through to find the right one.
View 2 Replies View RelatedI have created an XLA using a blank workbook which has a macro (called CompareMan) I have written within it.
I have written a utility (called INSTALLMAN) to install this add in.
When I run the install it creates and activates the add in OK.
The install also creates a toolbar with a button to trigger the macro.
Problem is, when I click the newly created button, no matter which workbook I am currently in I get a message ...
"The macro INSTALLMAN.xls!CompareMan cannot be found".
So my question is, how do I get my button to trigger the macro in my add in?
I have to do the same procedure for a number of profit centers. The only differences are the names of these profit centers. Therefore I want to use the same code, with changing references. How can I use this array so that it calls/uses 1 profit center at the time? If I use the following code they all are used at once (P1100P1200P1300P1500P1600P1900)
View 2 Replies View RelatedIs there a way to call a sub with out actually saying its name? In other words can I refer to a sub by a value or a cell that holds its name? for example, in my CNC programs I often use something like this;
View 14 Replies View RelatedOkay, I know you can reference worksheet functions with code like:
application.search()
But how do you use a UDF function within VBA???
I tried, thisworkbook.UDF() (UDF being the name of any UDF) but it didn't work.
I have a add in for excel (self made) and I recently hit a wall when trying to add functionality to it.
I have a form "UserForm1" created within the .Xlam file of the add in.
I want this form to be useable for any excel file that might be open, so putting code in the excel file that needs to use the form is undesirable.
The functions carried out by my add in are activated trough ribbon buttons.
my goal is to have a button on the ribbon that loads the form.
As the UserForm1.show code doesn't work, I will need a alternative.
I did find the code for making the sheet show if there would be a button in the workbook, however I want all code to be in the add in.
I am using the following code to find the text "Employment" in any row in colum H. If found it copies it to another worksheet.
Sub CopyRowsSht8()Sheet8.SelectSet Rng = Sheet8.Range(Sheet8.Range("H1"), Sheet8.Range("H" & Rows.Count).End(xlUp))For Each cell In RngIf cell.Value = "Employment" Thennum = cell.RowSheet8.Range("A" & num & ":" & "L" & num).Copy Destination:=Worksheets("Employment").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)End IfNextCall CopyRowsSht9End Sub
1. This sub calls CopyRowsSht8 and then this calls CopyRowsSht9 and so on. I have 15 sheets so how would I code this into some kind of loop to include sheets 8 to 22 instead of calling sub then sub then sub etc.
2. It takes a while to do just two sheets so 15 would be very slow - can this be made faster. The maximun number of rows containing data is only about 100 on each sheet -could that make a difference?
Im trying to call another subroutine within the same workbook but it doesnt work,
---------------------
Sub Macro1()
Dim mac1 As Variant
Dim mysub As Variant
Select Case Cells(1, 3)
Case mac1
Call mysub
End Select
End Sub
---------------------
The below thing has been driving me up a wall for 2 days now. I know the fix has to do with calling the variables, but I can't figure out the exact code.
Sub Parsing_Logic()
Dim IBG_URL As String
Dim A As String
A = Mid(IBG_URL, Application.WorksheetFunction.Find("/200", IBGURL), 8)
Dim B As String
B = Left(IBG_URL, Application.WorksheetFunction.Find("/200", IBGURL))
Dim C As String
C = Right(IBG_URL, Len(IBG_URL) - (Len(A) + Len(B)))
Dim D As String
D = Left(C, Application.WorksheetFunction.Find(".200", C))
End Sub
Function IBGLink(IBG_URL As String, Formatted_Date As String)
If Application.WorksheetFunction.IsErr(A) Then
IBGLink = (D & Formatted_Date)
Else: IBGLink = (B & Formatted_Date & D & Formatted_Date)
End If
I am trying to call another macro, but instead of using the name of the macro, I have it in a variable. I am getting an error that says "Compile Error: Expected sub, Function, or Property." Can you call a macro that is in a variable? I basically have a bunch of macros created. I also have a list of the name of those macros on a worksheet in Excel. I want excel to start at the top of the list in excel and run the macro, then move down and run the next macro, and so on. Below is my code.
Sub Commercial_2005()
Dim Macroname As String
Workbooks("bleeg.xls").Activate
Worksheets("CommercialList").Select
Cells.Range("a1").Select
While ActiveCell.Value <> ""
Macroname = ActiveCell.Value
Workbooks("copy of recast_Report_v2.xls").Activate
Call Macroname
Workbooks("bleeg.xls").Activate
Worksheets("CommercialList").Select
ActiveCell.Offset(1, 0).Select
Wend
End Sub
