Create Macro Code With VBA Code
I'm trying to add buttons to an excel spreadsheet at runtime. Each row in the spreadsheet should have its own buttons. I was able to create and edit them with the ActiveSheet. OLEObjects.Add() function, but after that, when i was trying to create code dynamically to react on the buttons' click events excel crashes (actually it works for one button, but not if my routine for adding a new button and event code is called more than once in a row!)
The code below works if the AddCmdbuttonWithCode() is called once, but crashes if it is called two or more times. Excel tries to restore the document after the crash and the first button and its corresponding click event code is visible and works... but NOT the second button and its event code...
The only way I can create multiple buttons right now is by calling my method once per click, opening the vba editor, changing the parameters for my AddCmdbuttonWithCode() routine and execute it again. After that I have mutliple buttons in different lines which all work fine (so the concept seems to work).
The problem seems to be the insertLine method, since everything seems to work if i leave it out (except for the code generation of course, since this is done by this part of the code :-) ). Is it possible that calling the insertLine Method can't be called multiple times? I don't know... any ideas? Feel free to test my code - it's small, easy to understand and has comments.
'this code calls the method which creates the buttons in specific cells with specific names
Private Sub CommandButton3_Click()
'the first call always works!
AddCmdbuttonWithCode "Edit_111_111_114", 23
'the second one crashes excel
AddCmdbuttonWithCode "Edit_111_111_115", 27
End Sub
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
VBA Macro Code To Create Multiple Charts
I need to create 63 charts from data which I have in two columns. I want to create multiple charts using one macro. For the first chart I want it to use cells K2:K80 as the x values, and M2:M80 as the y values. For the next chart I want it to use cells K81:K159 as the x values and M81:159 as the y values. For the next chart I want it to use cells K160:K238 as the x values and M160:M238 as the y values. I want to continue this, creating a chart for every 78 cells of data, all the way until the 63rd chart which uses K4900:K4978 as the x values and M4900:M4978 as the y values. I have created the following macro by " recording." This macro generates the first chart that I want: Sub Macro5() ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$K$2:$K$80" ActiveChart.SeriesCollection(1).Values = "=Sheet1!$M$2:$M$80" ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveWindow.SmallScroll Down:=-3 End Sub How can I alter this macro to create all 63 charts?. It seems like there is an easy way to do this, but I don't use macros very much (at all).
View Replies!
View Related
VBA Macro Code Needed To Run The Code Contained Within A Cell
I have a macro that, when run, needs to read the contents of cell B5, and run the code that it contains. Cell B5, for example, would contain the text: Range("B13").Formula = "SUM(D12:D14)" I need a macro to "execute this code", as if it were in the macro itself. I have assigned the above to a variable, but am not sure how to execute it. EG. Dim the_calc the_calc = Range("B5").value Now, how do I run the_calc ?
View Replies!
View Related
Efficient VBA Code Vs Macro Recorder Code
What is the most efficient VBA code for the following macro recorded codes? I wish to write more efficient code versus the lengthy, cumbersome macro recorder code. 1) Macro Recorder Code to Copy One Sheet to Another Sheets("Data Apple").Select Cells.Select Selection.Copy Sheets("Banana").Select Cells.Select ActiveSheet.Paste 2) Macro Recorder Code for Replacement Purposes......................
View Replies!
View Related
Create Comparison Code In VBA
I have to do monthly comparisons of two excel worksheets, one being a vessel timesheet and the other being an excel download from manpower software that we use. The comparison that is being made are days of the month, whilst the unique identifier between the two sheets (To make it easier to understand I will call them sheet1 and sheet2) is an employee ID. Both sheets, alongside the name and employee ID, list all the days of the month that employees have worked. If they have worked, there is a "1" under that day, if they have not the cell is blank. What I currently do manually is: 1: Search for the employee ID (and/or name) in sheet1 and compare the days with the employee ID in sheet2. 2: Highlight the differences 3: Move onto the next employee ID All differences are highlighted in sheet2. Differences highlighted are in the days only, where if any days are missing/extra in either sheet, then the particular cell(s) are highlighted in yellow. If an employee is missing from sheet1 but is in sheet2, then all the days are highlighted (in sheet2). If an employee is missing from sheet2 but is in sheet1, then that particular employee is copied to the bottom of sheet2 (after last row). As this is horribly complicated to explain, I can attach an example if this is sounding rather inexplicable? Is it possible to attach a xls to a thread?
View Replies!
View Related
Using VBA To Create VBA Code On The Fly
I have a vba module that reads a value from a txt file and saves that value in a variable called "iniInfo". I want to permanently assign that value programatically by using VBA code to create a Public variable e.g. "PUBLIC CONST conFIg = iniInfo" in my modGlobalVar Module. (Of course, this would only create the code if the "conFig" variable doesn't already exist.)
View Replies!
View Related
Create UserForm With Controls Via Macro Code
This code create a userform on the workbook opening .I'm having problems with 1. Closeing the Visual basic window after this procedure runs 2. Being able to use the create userformstext box value in other procedures 3. how to delete the userform on close Sub AddUserFormInputRequest() Dim objVBProj As VBProject Dim objVBComp As VBComponent Dim objVBFrm As UserForm Dim objChkBox As Object Dim x As Integer Dim strCode As String Dim firstLine As Long, SecondLine As Long Set objVBProj = Application.VBE.ActiveVBProject Set objVBComp = objVBProj.VBComponents.Add(vbext_ct_MSForm) With objVBComp . Name = "InputRequest" . Properties("Width") = 200 .Properties("Height") = 100......................
View Replies!
View Related
Create Multiple Buttons All Assigned To Macro Code
I'd like to write a macro to create buttons with the caption "Fix" in over 300 cells. Furthermore, I would like each button, when pressed to run a macro that would copy and paste the values (paste special) of the entire row in which the button is situated as well as copy and "paste special" the values in the fixed cells $J$2, $K$2,$L$2, and $M$2. I've attached a file to clarify what I'm sure is an extremely convaluted statement of my problem.
View Replies!
View Related
Create Surface Chart Via Macro Code Based On Selection
I want to write a macro to add a surface chart for a Data Range which includes 6000 data points. But the series selected for the chart don't cover all Data Ranges, only part of them. Public Sub AddChart2(LastRow As Integer, LastCol As Integer) Dim cht As ChartObject, currentSheet As Worksheet Dim rng As Range, newRow As Range, srcRange As Range Dim colIdx As Integer colIdx = 5
View Replies!
View Related
Display All VBA Code In Code Window (VBE)
When I started using VBA, all the code for each module was shown in the code window. This was a bit cumbersome when looking for one sub in particular. Now when I load VBA, all the Subs and Functions have their own page, and can be selected from the right hand drop down list. This is much easier for entering code, and finding subs. However, it is sometimes useful to see 'the big picture', so how do I swap between the two types of display?
View Replies!
View Related
Macro To Delete Vba Code - Macro Security Warning
I have recently grabbed the very helpful "Macro to delete VBA code" from this site, and it's working but with one small problem. My worksheet runs a lot of code, then deletes all macro code and saves itself. The problem is the next time i open I still get the macro security warning! I've checked thoroughly and there is definatley no vba left anywhere. If I open the document, enable macros, and save it, then open it again, I no longer get the warning.
View Replies!
View Related
VBA Code To INSERT CODE - GURUS
I have a worksheet named mylist, that I delete, then recreate everytime I run a specific macro. However, I have code in this worksheet that I require, so is there a way for me to insert code to "insert code" on the "re-creation" of the worksheet?
View Replies!
View Related
VBA Code- How Do You Get The Code Boxes To Appear In Threads
I have code that inserts columns, inserts formulas, and then copies the formulas to the last row of data. It all works good but for some reason, the columns that are being copied, are being copied past the last row of data. It can always be determined how far down it will be copied. Examples: if the last row of data was row 4, then the formulas would be copied down to row 24, if the last row of data was row 54, then the formulas would be copied down to 254, if the last row of data was row 284, then the formulas would be copied down to 2284. I can not figure out how and wh this is happening, but whatever the last row of actual data is on the spreadsheet, there is a 2 being placed in front of the last row of data and the formulas are being copied down to whatever the last row is with the addition of a 2 in front. Here is the code for the columns additions, and formulas: Columns("O:S").Insert Shift:=xlToRight Columns("W:Z").Insert Shift:=xlToRight Columns("AB:AF").Insert Shift:=xlToRight Columns("AH:Al").Insert Shift:=xlToRight Range("O2").FormulaR1C1 = "=CONCATENATE(RC[1],""/"",RC[2],""/"",RC[3])" Range("P2").FormulaR1C1 = "=IF((RC[3]=7),LEFT(RC[-2],1),LEFT(RC[-2],2))" Range("Q2").FormulaR1C1 = "=IF((RC[2]=7),MID(RC[-3],2,2),MID(RC[-3],3,2))" Range("R2").FormulaR1C1 = "=RIGHT(RC[-4],4)" Lets take this first one for example: Range("O2:S2").Copy Range("O2:S2", Range("O2:S2" & Range("A" & Rows.Count).End(xlUp).Row)) If the last row of data was row 54, then columns O:S would have the formulas copied down to row 254. How can this code be modified so the formulas will not be copied past the last row of data? How do you get the code boxes to appear in these threads. I do not know how to do this. As you can see, all I did was copy and paste my code in this thread.
View Replies!
View Related
VBA Code Which Deletes A Macro
I’ve got a macro that automatically starts when the workbook is open. The macro finishes with Application.Dialogs(xlDialogSaveAs).Show The document will be always saved with a new name. As I just need the macro once when the workbook is created I’m looking for a code that deletes the macro before the workbook is saved. I tried to record a macro while I was deleting another macro, but apparently it is not working (the recorded macro is empty). Does anyone know if there exists a code to delete a macro?
View Replies!
View Related
VBA Code For Autofilter From Macro Recording
The code contain using Autofilter to some columns, & then copy paste to another sheet to first blank row, & sorting it by asscending in Column A. Both sheets are in the same workbook. But for some reason, the Macro will giving the result expected if i run it step by step (by using F8 button under window VB editor). And if i call the Macro name it stoped in the middle of the process.
View Replies!
View Related
Editing A Macro VBA Code To Take A Password
i currently have created a macro for copy a selection of cells from one work book to another. The file were i am copying the cells from is Track_&_Trend_00.xls. In this there is a password to open and modify. The sheet that the cells are being pasted to is TRACK & TREND.xls. There is no password on this. My macro runs fine only that midway through it asks for the password to open and the password to modify for the sheet Track_&_Trend_00.xls. I wonder ifany one would know about entering input lines of code below to automatically put these passwords in and not having the two password dialog boxes pop up midway through running the macro. Range("A8").Select ChDir "T:Track & Trend" Workbooks.Open Filename:= _ "T:Track & TrendTrack_&_Trend_00.xls" ActiveWindow.SmallScroll Down:=-102 Range("A8:S115").Select Selection.Copy Windows("TRACK & TREND.xls").Activate ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save
View Replies!
View Related
Line Numbers In VBA Macro Code
If I number my lines of code and an error occurs, the use of Erl in the VBA will then return the exact VBA line number that the error occurred on. Is it possible to automatically capture or record that same effect when the macro process leaves its current sub and goes to another? Reason: I'm trying to create a Call Stack that can be reported through an Error Handler that will include the exact location and process that the error occurred to better troubleshoot and understand where and why the error occurred. The best I can come up with so far is manually putting in bookmarks along way so I know how far along the macro went before the error. From all of my searching I believe retrieving the Excel Call Stack is not possible and so one must be manually created. Enclosed is an example of what I have so far. It goes through several macros and logs the Call Stack. It’s a work in progress so it is a little sloppy looking but it is functional. If a Sub finishes it is then taken out of the Call stack. Several "BookMarks" are placed to give an idea of how far along the Macro has gone within that Sub. The Code for the Erl example is: Sub SampleErrorWithLineNumbers() Dim dblNum As Double 10 On Error Goto PROC_ERR ' Errors if table doesn't exist 20 Select Case Rnd() Case Is < 0.2 30 dblNum = 5 / 0 40 Case Is < 0.4 50 dblNum = 5 / 0 60 Case Is < 0.6 70 dblNum = 5 / 0 80 Case Is < 0.8 90 dblNum = 5 / 0 100 Case Else End Select 110 Exit Sub PROC_ERR: 120 MsgBox "Error Line: " & Erl & vbCrLf & vbCrLf & _ "Error: (" & Err.Number & ") " & Err.Description, vbCritical End Sub
View Replies!
View Related
Calculate IRR In VBA Macro Code
I used the following statements to (try to) calculate an IRR in my VBA program and received a runtime application error 1004. Tried several variants of the statement but cannot seem to escape the error. Dim IRRValues(12) For iIRR = 65 To 74 For iTIGRcubYear = 1 To 11 IRRValues(iTIGRcubYear) = Worksheets("TIGRCub"). Cells(iIRR, 3 + iTIGRcubYear) Next iTIGRcubYear aIRR = Application.WorksheetFunction.IRR(IRRValues, 0.2) Worksheets("TIGRCub").Cells(iIRR, 14 + Scenario) = aIRR Next iIRR
View Replies!
View Related
Sort Range Via VBA Macro Code
I recorded this macro - which was a simple copy and paste and then sort the results, however it works in excel 2007 and not in 2003. Even tried to record the same in excel 2003 and it still does not work. It seems to fail at the sorting stage Sub sortprices() Sheets("Rates").Select Range("B229:C241").Select Selection.Copy Sheets("Results").Select Range("C4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveWorkbook.Worksheets("Results").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Results").Sort.SortFields.Add Key:=Range("D4:D16") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Results").Sort .SetRange Range("C4:D16") . Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("C4").Select End Sub
View Replies!
View Related
Worksheet Function In VBA Macro Code
I am trying to write a function in excel to use the worksheet function "small" In the vba immediate window it doesn't return anything and in the spreadshhet I get a # NAME? error when called in a cell the code is below. Sub UseFunction() Dim myRange As Range Dim answer As Integer Set myRange = Worksheets("Sheet1").Range("A1:F6") answer = Application.WorksheetFunction.Small((myRange), 1) MsgBox answer End Sub
View Replies!
View Related
Conditional Copy VBA Macro Code
I have 2 workbooks that I am working out of. Workbook number 1 is where the data is going to be saved from workbook number 2. I need to copy data from workbook number 2 but there are several road blocks. 1) I need to search for specific information in column E. Anytime information is listed under this colomun it is listed 2 times one on top of the other with a different peice of information to the right of each one. note that the items that I am looking for on this sheet will no always be there. What to copy under E / F E F 1 BAR | $5,123 2 BAR | 24 Once the first "Bar" has been found, F1 & F2 need to be copied to workbook 1 to different locations on the active sheet. The names will always be listed together and the data will always be to the right.
View Replies!
View Related
Running An Excel Macro Using VBA Code In Ms Access
I am attempting to run a MS Ecel macro that is stored on a MS Excel shared file in a MS Ecel target file (locally stored on my C drive) from a VBA module in Ms Access. That is, from a code i want to open the .xls file that houses the macro, and then open the .xls file that i want to run the file in, and then run the macro. Here is my situation: the file that houses the macro has the workbook hidden and causes in error. if i unhide the workbook i get a Run-Time Error 91...object variable or With block variable not set. if i hide the workbook the vba coding can't find the macro...run time error 1004 once again, i currently have an excel file that houses macro whose workbook is hidden. does anyone know how this can be accomplished? here is the ....
View Replies!
View Related
Assistance With Creating A Specific Macro Using VBA Code
I was wondering if someone could help me generate a Macro to do the following: I have a sheet with the following characteristics. Column A, rows 8-15 contain headers Column A, rows 17-24 contain headers Column A, rows 26-40 contain headers Column B to CV, row 6 may or may not contain an "X" Column B to CV, rows 8-15 & 17-24 & 26-40 may or may not contain the various data I need a macro which does the following on the press of a button located somewhere on the sheet: Generate a .TXT file in the folder C:Test with the name "Test_YYYY_MM_DD_HH_MM_SS_Full.txt" which uses the system time and date to fill in the values In the TXT file the following data should be created: ....
View Replies!
View Related
VBA Macro Code To Check Spelling In Range
Is there any way to use formula or VBA to highlight cells which contain the correct spelling? For example i have columns A filled with words in each cell but need to highlight which ones have the correct spelling, as there are more with incorrect so i need to visually see the correct spelling.
View Replies!
View Related
Run VBA Macro Code On Protected Worksheet
I have incorporated print/print-preview command buttons/VBA into a workbook with protected sheets. To enable these command buttons to function when the relevant sheet is protected, I have had to add VBA code to unprotect the sheet before generating the print preview, and then to protect it again afterwards. However, the code I have used (see below) prompts the user to enter the protection password, is there any code I can use where I can write the password into the code itself to unprotect the worksheet without the using having to enter the password? 'Unprotect Sheet ActiveSheet.Unprotect 'Print preview & cell formatting code 'Protect Sheet ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingColumns:=True
View Replies!
View Related
Step Through & Debug VBA Macro Code
I don't understand the relationships that are happening and what the final value would be if for example the PT was Red. I'm confused and was curious how four lines could all equal different values(highlighted). UpUserPaint = UCase(RegPaint.Value) Dim icount As Integer LenRegPaint = Len(RegPaint.Value) For icount = 1 To LenRegPaint ValPAint = ValPAint + Asc(Mid(UpUserPaint, icount, 1)) Next icount If PTRed.Value Then ColorCompare = Abs(Pallet * (ValPAint + PrdtSeed - LenRegPaint)) ColorCompare = Abs( Round(ColorCompare / PrdtSeed - 7227, 0)) ColorCompare = ColorCompare And 6215971 ColorCompare = ColorCompare Xor 6215971.............................
View Replies!
View Related
Macro Code To Create Custom Menus & Sub Menus
I am using the following code to create a custom command menu. Sub AddMenus() Dim cMenu1 As CommandBarControl Dim cbMainMenuBar As CommandBar Dim iHelpMenu As Integer Dim cbcCustomMenu As CommandBarControl On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete On Error Goto 0 Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar").......................... I want to add a new button control at the bottom of the menu underneath the holiday control but it keeps adding it on the sub menu, which leads off the holiday button control. I want the menu to look like this. Open Net 2 Access Add Employee Edit Employee Delete Employee Holidays (3 options on sub menu) *New Control
View Replies!
View Related
Assign 2 Macros To 1 Button (or Adding VBA Code To The Existing Macro)
I'm verrry new to editing VBA code and I've been trying to figure out how to send a column of data ("Min Data" AB3:AB21 from "Sheet1") to a worksheet ("F4" - Min) and sending another column of data ("Max Data" AC3:AC21 from "Sheet1") to a different worksheet ("F4" - Max) by using only the one button ("Record Data" from "Sheet1" I've been trying to figure out ways to edit the VBA in "Module 1" to make this work Can anyone help me out here? If what i'm asking for isn't clear enough, just let me know and i'll try to explain in more detail.
View Replies!
View Related
Lookup Via Macro Code: Find The Match And Then Select That Cell In VBA
I have a large database of equipment on one sheet and an input form on another. For inventory control, when a user scans a number into the input form, the main inventory sheet is updated with the current location. I have it working using functions, but I need to do this in VBA. I am looking to do the following; User enters 2222 on the input form. The code matches 2222 on the inventory sheet, moves the activecell 10 columns and updates a value in that cell. Cell A2343 is "2222" then Cell K2343 is changed to "WAREHOUSE 4". What is the best way to find the match and then select that cell in VBA?
View Replies!
View Related
How To Create A Unique Code
Using a formula, How can i get excel to create a custom unique number using the first three (or two) characters from another field and then adding four unique numbers to the characters. basically heres the info Field H2 contains the characters "ACS" so i would want another field to create a unique code to include something like "ACS1234" and to continue to the next row would be the first three characters of field H3 then 1235.
View Replies!
View Related
Create Event Procedure Via Code
I'm trying to add a worksheet event via code. It works OK when I run it on its own but I get a run-time error 9 subscript out of range error when I try to run it from another procedure. Please find attached my 2 bits of code. Any help greatly appreciated. This is the bit that works OK on its own but not when called from my other procedure Sub Code_To_Write_Code() 'This writes code to the new sheet Dim StartLine As Long Dim SheetToAddCodeTo SheetToAddCodeTo = ActiveSheet.CodeName With ActiveWorkbook.VBProject.VBComponents(SheetToAddCodeTo).CodeModule StartLine = .CreateEventProc("Calculate", "Worksheet") + 1 ..............
View Replies!
View Related
Modify Code To Create Worksheets
I have this code that will create 3 worksheets with the name "New Name" and the # ( 1/2/3 ) .. How would I change the code if I want 3 worksheets with different name? Like Sheet1 then Sheet 2 then Sheet3 or any other name? 'To add worksheets and change name with one code For i = 1 To 3 'Creates 3 worksheets Set ws = Worksheets.Add ws.Name = "New Name" & i Next i
View Replies!
View Related
Code To Create Customer Number
I have the following formula copied down from A3 to A200 in a spreadsheet, which creates a unique standardized customer number for the each names listed in B3 to B200: =IF(B3="","","S"&LEFT(B3,1)&"-"&TEXT(SUMPRODUCT(--(LEFT($B$3:B3,1)=LEFT(B3,1)))*10,"0000")) Therefore, with the following names listed in B3 to B10: UPS FedEx Fisher Price Bell Canada Grand & Toy Rogers Cable Dominion Blue Jays The respective customer numbers would be: SU-0010 SF-0010 SF-0020 SB-0010 SG-0010 SR-0010 SD-0010 SB-0020 I would like these numbers to be static, so I would appreciate it very much if someone can give me a VBA code to replace the formula.
View Replies!
View Related
Create Chart Automatically Via Code
to write a code that would create a chart automatically in another worksheet when a button is clicked. I have attached the sample data that I am working on. Each row in the sheet represent 3 coordinates, which are: coordinate # 1 = start, 0 coordinate # 2 = centre, level coordinate # 3 = end, 0
View Replies!
View Related
Code To Check For And Create New Folder
I have a macro that creates a spreadsheet on a weekly basis and have been using it for quite sometime. Due to the large number of spreadsheets I would like to incorporate some code that would create a folder every month. I would assume that the code would need to include a check routine to see if a folder for that month exsists.
View Replies!
View Related
Parse HTML Code, Create A Table
I have a function that locates a table on a webpage and pulls the html code into one cell in a worksheet. Basically we can call this one cell a text file. I need to parse through this text file (cell A1) to create a table. This text file only has info for 1 table, the table always has 12 columns, the rows are variable. I would like it to then write back this parsed text file back into excel as a table, say starting in cell A2 on the same worksheet.
View Replies!
View Related
Code To Create Directories From Cell Values
I have a CSV file and would like to create directories on my hard drive and the names of those directories will need to be pulled out from this csv file: [B1:B86]. For example I have the following data in my csv file: B1: folder1 B2: folder2 . . . And would like to create folders on my hard drive so that folder names will be: folder1, folder2, ...
View Replies!
View Related
|
TRACKER
