I have 60 checkboxes on my spreadsheet and basically, when any of the checkboxes are clicked on, the same action should be performed.
The only way I know how to do this is if I were to write the same code 60 times... one for each checkbox_click() method.
I have a combobox which is used to select data (stock symbol) from a list. Following selection of the stock symbol, a VBA routine is invoked from a radio button which accesses the internet to get data for the selected stock. Currently, this is a two step operation. I tried to cut out the need to click on the download button by calling the GetData sub inside the combobox sub as shown in the code below.
View 14 Replies View RelatedWhy the second sub is not executing?
Code:
Private Sub RolloverYear_Click()
Application.ScreenUpdating = False
Dim s As Variant
Dim resp As Long
resp = MsgBox(prompt:="You are about to clear ALL current year payroll information. Are you sure you want to continue?", Buttons:=vbYesNo)
If resp = vbNo Then
[Code] .......
VBA jumps out of my called subroutine which is stored in a Module halfway.
When I call the SortLec() sub, it runs until y1 = Application.Match(MC & "-" & CI, rng, 0), and then it just jumps back to the SUB commandbutton1_click() IF statement. Why is it that VBA skips the rest of the code from my sub?
Option Explicit
Private Sub CommandButton1_Click()
Dim i As Integer
Dim numofrows As Integer
Dim workbook_directory As String
Dim file_name1 As String
[Code] ............
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
---------------------
in writing a subroutine that needs to ask the user
1) tax file number
2) Income
3) tax withheld
It then needs to use a function that i already made called 'incometax' that calculates 'taxpayable' and use that to calculate taxreturn (difference between 'taxpayable' and 'taxwithheld').
I then need it to output like this in a message box
Estimate for tax file number: .....
Created on ......
Total income: ......
Tax witheld: .......
Tax payable: ......Tax return: .......
Is calling a subroutine within another different than running it from the play button in the VB editor? I am getting different results.
I have the following code that I want to call from another subroutine that creates a series of command buttons.
Sub DynamicButtons()
Dim ButtonCount As Integer
Dim ctl As OLEObject
' Create the Button objects
ButtonCount = 0
For Each ctl In Sheets("Sheet1").OLEObjects .............
if it is possible to use a batch query to execute more than one SQL statement at once from Excel. Something like this:
View 9 Replies View RelatedI just want to have a column sorted by Z-A order and then A-Z by the same button, I recorded 2 macros on 2 buttons and tried to join codes with "if, elseif".
View 6 Replies View RelatedI wanted to know If there a way to hide the process of executing macrob (vba)?
I have a long macro that runs on a file and I want to hide the process. I know there is an option to it, I just don't know it.
I have a workbook with several worksheets. The numbers entered by the user in the cells C7:C9 of sheet "A" will be used to calculate some values in sheet "A" as well as in sheet "B".
Calculations on both sheets involve macros: macro "scale" on sheet "A" and macro "sort" on sheet "B".
I used this code to execute macro "scale" on sheet "A" triggered by the Worksheet_Change function:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C7:C9")) Is Nothing Then
Exit Sub
Else
scale
End If
End Sub
now, I want to execute macro "sort" on sheet "B" practically at the same time (with the same worksheet_change condition for sheet "A")
All off my attempts resulted in running "sort" in sheet "A" which is not what I want. I couldn't get it to execute the macro in the other worksheet.
Already tried "With Worksheet("B") and "Dim ws As Worksheet, Set ws = Worksheets("B") - no luck...
The error trap does work when there is an error, but it also runs at the end of the code when there isn't an error... I'm not sure what I've done wrong. When I test with data that is correct, it still runs through the code when it gets to the bottom. Have I put the error trap in the wrong place?
I have put in bold the section of the code with the error trap.
Option Explicit
Const TargetDB = "Group Life Pricing Tables v0.1.accdb"
Sub HMUK_Val()
Application.ScreenUpdating = False
'define variables to be used in the process
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sSQL As String
Dim SchemeInfo As Integer
Dim CurrentAge As Integer
Dim CurrentRow As Integer
Dim CurrentSex As String
Dim Message
'check that Scheme data has already been entered
For SchemeInfo = 1 To 13
If Sheets("Working Info").Cells(SchemeInfo, 2).Value = "" Then
Message = MsgBox("Please ensure all Scheme information has been entered before processing member info", vbOKOnly, "Warning")
Exit Sub
End If
Next SchemeInfo
(Excel 2003)
I've a macro (I recorded) that executes a query (Oracle) and returns rows that exceeds the max (65539). How do I go about adding another sheet therefore allowing the query to continue?
I know how to do it via opening a text file and importing, but do not know how to do it with the use of a query.
I have a series of checkboxes that I would like to select/deselect on mass, without having to manually click them individually...
Each of the CheckBox_Click routines change the visibility properties of images.
Changing the CheckBox value with code (true/false)
CheckBox.value = True
will alter the checkbox, but not execute its code...
And
Run "CheckBox_Click"
did not do it either.
How do I simulate the clicking of the CheckBox, with code?
I have a Worksheet_Change method in my sheet module which has suddenly begun working selectively. I have several target ranges in my worksheet that I need to watch for changes. One range of cells is all pull-down menus (a list of electrical loads through excel's validation), another two cells are looking at the name and location of an electrical panel.
When the user selects an electrical load, the worksheet_change method updates a legend of definitions. When the user enters a new name or location, the method updates similar fields elsewhere on the same sheet.
The problem is, excel has stopped executing worksheet_change when a load is chosen or location entered. It only executes when the aforementioned load and location are deleted. However, the name field triggers worksheet_change just fine!
I have attempted to put breakpoints on the worksheet_change method; Excel does not even execute the method as described above. I've tried breaking on the toggling of Application.EnableEvents (which occurs early on in the execution of worksheet_change). I currently have a global watch on Application.EnableEvents and I'm quite sure it's TRUE before I attempt to trigger a worksheet_change event.
My next step is to create a brand new workbook and copy my spreadsheet and code into it. I'll post the results of that experiment.
What I want to do is make a simple text link inside of a cell so when it's clicked on it executes the CommandButton(x) VBA script.
I'm guessing =HYPERLINK is going to be the best method.
Like so:
=HYPERLINK("Forms.CommandButton1","E-Mail")
Is it possible to refer to an object which is in another subroutine?
Or should i have to give its definition in each sub i need it?
I need to allocate an existing subroutine (its code below) to a new button I'd like to add to my sheet. Two questions arise: to use "form control" or "activeX"?, also should I redfine the first line of the code?
[Code] .........
I want to pass the name of the routine as a parameter.
View 6 Replies View RelatedMy code passes a string created by a function to procedure. It all works great, but i need to add something so that if the string = false then sub doesn't run. What's the best way to do that. Here is some of the
View 6 Replies View RelatedI met problem in calling into a subroutine. It is error 424. I have dim all variable. However I still get it.
Code:
Sub UpdateAll()
'
' UpdateAll Macro
[Code]....
I got the error when I pressed F8 to step into the getOneRecord() function, which is highlighted in red.
My code defines a variable: newrow. Then I call a subroutine using the Call command.
The subroutine does not recognize newrow.
It says it has value zero. How can I pass the value of newrow on to the subroutine.
1. Can a Subroutine be called from within a user-defined Public Function? How?
2. Is there a difference between calling a subroutine with a 'Call Sub_Name' statement, vs. calling the subroutine with an 'Application.Run'(?? or similar) statement?
What is the syntax for executing a 'SHIFT+xlUP' function in an Excel macro?
View 4 Replies View RelatedI am trying to see if it is possible to create an intentional delay when executing a macro. Basically I would like to pause the macro for 5 seconds and then continue with the rest of the code.
Is such a thing possible with Excel?
I have a workbook which autosaves and closes if no data is input for 10 minutes. It resides on a drive accessable by all our team and is set so that only one person can have read/ wrote access at a time. It tests for read only, shuts down if it is and saves and shuts down if not.
I have noticed that it doesn't work if the workbook is waiting for input.
I.e. someone starts to enter text, or something, in a cell, then goes off to make a coffee or whatever leaving the entry cursor sitting there blinking. It will not shut down after the ten minutes with no input.
I have finaly completed my project to include all the bells and whistle needed to make an ok task a bit more exciting. However, i am having a problem when the code is executing. It seems to "break" at random points in the process. I am not able to duplicate this error at my home (nor do i want to) and I can not quite figure out what is causing this as it only happens at my work computer. I have tried my coworkers computer and the code executes flawlessly like at my home.
Strangely enough, when the code breaks I am able to hit 'continue' and it continues on its way until the next hiccup. Please assist as this fluke is causing my brain to spiral.
if I can send text to a macro and have VBA treat the text as a line of code?
Suppose in a worksheet in cell A1 there is the following "text"
msgbox "hi there"
can you get vba to do the following:
Sub runtext()
Mytext = sheets(1).range("a1").text
XXXXXX Mytext
End Sub
where "XXXXXX" is a method or command that will cuase vba to evaluate the text string and run it as a line of code (returning a message box saying "hi there")
I don't want to send variables/objects to an existing method in VBA - I want to send the method to VBA....
I have a short subroutine to delete a row in a second worksheet (SUMMARY EXPENSES) if one is deleted in the main worksheet (AS CODES) as per below:
[Code] .......
In theory the row deletion should only be triggered IF dynamiccounter is smaller than staticcounter, but the fact is anything whatsoever triggers the row deletion. If I type text into a cell a row gets deleted. If I copy a cell, a row gets deleted. It works great at deleting the line, but I suspect it has nothing to do with the counters I set up and the reassigning of the counter value towards the end of the If statement.
I have a small sub routine below I would like to make a slight modification to. The routine currently references formatting relative to the Offset statement in bold below. Instead, I would like the formatting to come from a cell address listed in a cell just to the left of the cursor when the statement is encountered.
View 2 Replies View Related