Function Call To UserForm (VBA)
Aug 23, 2006
I wanted to have that one appear whenever a certain cell in a certain sheet gets a specific value, and depending on the answer of the user the Form should set the value of some other cell. So maybe there´s a much simpler solution to this task than what I tried - and I´ll be grateful to learn about it.
Anyway I tried the code which follows below - and it works just fine when I call it from the VBE environment - but inside the UserForm it just stops in between (without any error message) when I operate it from the Excel sheet (but still succeeds with the function). I´ll be very happy if someone could solve this "miracle" for me (even IF there´s a better solution altogether). So I just provided a simple Function which does nothing but call the UserForm and is executed whenever the condition is met (some calls to MessageBoxes in between just have been added to see how far the code gets executed): Function:
Public Function Call_my_form()
Call Option_Form.Show
MsgBox "Call executed"
End Function
Call of the function: =If(E11="Go";Call_my_form();"Nothing")
The UserForm just uses two OptionButtons with code as follows (two versions to check whether one of these would be the reason - both do behave the same way):
Private Sub OptionButton1_Click()...........................
View 5 Replies
ADVERTISEMENT
May 21, 2006
I have a work book with 3 sheets. Sheet 1 is the main sheet and sheets 2 and 3 will use (I hope vlookup) to update 3 columns from info in sheet 1. my attempt at a vlookup call in sheet 2 is: =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
my understanding is that: - $b3 is the cell in sheet 2 that will be updated as a result of the vlookup call. - [master_AoJ_2.xls]Sheet1!$B$3;$B$65 refers to range $B$3:$B$65 on sheet1 of master_AoJ_2. it does not work... infact I get nothing at all. when I type the call into cell $b3 of sheet2 excel thinks it is stariaght text. this is how I coded the function call =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
View 4 Replies
View Related
Nov 14, 2008
I've created a simple UserForm, and with some great help from royUK. I've managed to get it working to suite my needs. The next thing I need to do is call it when the user runs the macro.
How do I call the UserForm from within the macro that I created?
Once the user gives the input, how do I take the values and pass them to the loop in the macro?
View 11 Replies
View Related
Aug 30, 2007
Just wonder how to call the function if I have it's name in the variable String
for example something like that:
View 11 Replies
View Related
May 10, 2007
I wrote a function procedure in VBA. Pasted it into a 'VBAProject' sheet in my workbook. But when I try to call the function (by entering the name and arguments into a cell), the sheet displays "#NAME?".
What have I forgotten?
example:
Public Function TotDays( _EndDate As Date, _StartDate As Date _) As Integer
TotDays = EndDate - StartDate
End Function
cell
a5: =totdays(b2,A2)
b2: 4/1/2007
a2: 3/1/2007
View 9 Replies
View Related
Jul 12, 2007
I created a file with one sub and one function. Saved it as an add-in. and followed the promts to insert the Add-in.
The button I assigned to the macro works fine, but when I call the function, I am told "Sub or Function not defined".
Is there some special way to call an add-in function from VB?
View 9 Replies
View Related
Jul 22, 2009
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 4 Replies
View Related
Apr 26, 2013
I have some Worksheet_SelectionChange code that automatically calls a Userform when a cell is selected.
What I want to do is retrieve the data from the cell where the Userform is called from and enter it into a textbox on the Userform.
There are 3 other columns to the right of the cell that is selected.
If there is data populated there, I want this to be copied to the appropriate textboxes on the Userform too.
View 1 Replies
View Related
Dec 27, 2007
I have a control button on a worksheet that is suppose to call a userform. I am using the code
Frm_Customs.show
But I keep getting an error. The button was created from the control toolbox.
View 9 Replies
View Related
Sep 24, 2008
How is it possible to take the choice from the Combo Box and send that into a Stored Procedure statement in Excel VBA
I have 3 Combo Boxes, Months, Years, Date.
I'm trying to fillout these into this statement to run my Stored Procedure.
View 10 Replies
View Related
Mar 2, 2012
I have found some code that does what I want- but i do not understand how to use - call it. The programmer says ...
GetNetworkIPAddress()
' can be called from a worksheet cell using the formula:
' =GetNetworkIPAddress()
I have put this in a cell but i ger an error #Name?
what should i do ?
View 9 Replies
View Related
Aug 12, 2008
I'm playing around with the Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) function. I have a list of people in Column D that I'd like to move data around if the user double clicks on a cell in Column D. The list is about 158 rows long, and could grow beyond that. I've gotten lazy typing:
If Intersect(Target, Range("D4") Is Nothing Then
Exit Sub
Else
{My Code Here}
End If
If Intersect(Target, Range("D5") Is Nothing Then
Exit Sub
Else
{My Code Here}
End If
Etc, Etc, Etc
What I was hoping to do is use a variable (t) to cycle through the rows and call the various functions. So what I came up with is:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
On Error Resume Next
Dim t As Integer
Dim Subtest As String
For t = 4 To 158
Subtest = "D" & t
I've created Sub D4(), Sub D5(), and Sub D6() as a test. This didn't work at all as it's looking for a Subtest(). Also, clicking on D5, D6, didn't do much either. Is there any way to make this cycle work, or should I keep typing away?
View 9 Replies
View Related
Apr 3, 2008
I have created an Excel add-in (.xla) which contains many custom functions. This add-in is stored as read-only on a network drive. I reference it from various spreadsheets and calls to it work perfectly unless I put a call to it in the Workbook_Open event. When I make a call to a function in the add-in from a spreadsheet's Workbook_Open event, Excel crashes completely and I get the dialog that offers to recover the file and report the problem (i.e. Excel shuts down). I have no doubt that this is being caused by the call to the add-in specifically from the Workbook_Open event as removing this call solves the problem. Also, it is not related to a particular function as I have tried various ones; it is ANY call to the add-in from Workbook_Open that causes it. Is this a bug in Excel or is there something that I need to do before referencing the add-in in Workbook_Open? I have tried Application.CalculateFull and Doevents prior to making the call to the add-in and neither approach worked.
View 2 Replies
View Related
Jun 27, 2014
I want to be able to call one user form from multiple ActiveX command buttons. The problem is, the number of command buttons depends on user input on another worksheet, so it's variable.
I've renamed all of the command buttons so they are named "CommandButton" & i, where i is an integer between 1 and, say, 200. I want each of these buttons to direct to the same UserForm where additional information can be entered.
I can't think of a way around the event-handler procedure name.
Sub [Command Button Name]_Click
to call the User Form. I won't know the command button names, because I won't know how many there are (max i) until the user inputs.
Basically, I want to create a For loop through the max i and have the event-handlers call the user form
View 14 Replies
View Related
Dec 22, 2009
I'm using an activex combobox in excel. It display 4 posible choices, and one of them is "Other". When someone select "other" there is a textbox named "tb" that should be filled. Otherwise tb should be invisible. Combobox change() function looks like that:
View 14 Replies
View Related
Jan 9, 2006
I have a spreadsheet with 5 worksheets representing the working days of the week. Each worksheet contains data pertaining to every occurance of that day for as long as it has been recorded. Each day is broken up into hourly data.
So for example, for Monday's worksheet, you would have
Col A..............Col B........Col C
9/jan/06........5am.........data
9/jan/06........6am.........data
9/jan/06........7am.........data
...etc....then...
2/jan/06........5am.........data
2/jan/06........6am.........data
2/jan/06........7am.........data
...etc....
We are trying to create a function, on another worksheet called Query. The only way I can describe what we want it to do is to do it in psuedocode.
Psuedo........................................
View 10 Replies
View Related
Apr 23, 2007
I found a function to set the password a an excel file:
=========================================
Function SetPwd(strNoPwdFile As String, _
strPwdFile As String, _
Optional strOpenPwd As String, _
Optional strModPwd As String) ' As Boolean
' This function requires the following arguments:
' strNoPwdFile - The path to a document without a password.
' strPwdFile - The path and name to save the password-
' protected document.
'
But the compiler said that it is syntax error.
How can i rectify the error?
View 9 Replies
View Related
May 23, 2006
I want to call on a function with a macro/button in the same way as when you choose "insert-function" but in a way that the user himself is able to put in values on the spredsheet.
View 7 Replies
View Related
Mar 27, 2008
I have a written a small sub to run solver. However, I need to run a secondary sub at each solver iteration. The secondary sub I want to run is called “BypassGoal”.
My code currently looks like:
Sub OperatingPt()
Dim WrkBk As Workbook
Dim WrkSht1 As Worksheet
Set WrkBk = ThisWorkbook
Set WrkSht1 = WrkBk.Worksheets("Engine")
Dim Mode As String
Dim CellTarget As String
Dim CellRange As String
WrkSht1.Activate
WrkSht1. Cells(30, 2).Value = 2
CellTarget = "H14"
CellRange = "B30"
SolverReset
SolverOptions Iterations:=3000, Precision:=0.000001, AssumeNonNeg:=True, Derivatives:=2, StepThru:=True ....................
View 2 Replies
View Related
Jan 14, 2008
I could call another userform in a userform(like a new window)?
I want to put a MsgBox when i click in a buttom, but i prefer to use another Userform like a Msgbox because it's looks better than a typical msgbox.
View 5 Replies
View Related
Jun 25, 2013
When I step-through my code below, it always opens the first file in the directory "C:Pyramid Files", but when it comes back to the Pyramid Files sub after fully processing the first file via various other subs, the VB Editor apparently doesn't like something about this line: StrFile = Dir(), since it quits after "snapping-back" to the previous sub Initialize(). I have also tried StrFile = Dir, but that doesn't work either. I did Dim Strfile in the General Declarations. When I set Watches for Dir and Dir(), I get the value "Invalid procedure call or argument" for both, as if the directory function lost the value. I can't determine why this is happening.
VB:
Dim WSM As Worksheet, WSB As Worksheet, WS1 As Worksheet, [U]StrFile As String[/U], StrDirectory As String, ClientCode As String
Dim Filename As String, LastRowb As Long, LastColB As Integer, LastRow1 As Integer, NextRowC As Integer, x As Integer, y As Integer
[Code] .......
View 4 Replies
View Related
Aug 30, 2012
This error message in line vpp:
Invalid procedure call or argument
Code:
Function fn1(ByVal a, ByVal i, ByVal e, ByVal N, ByVal w, ByVal ta)
Pi = Application.WorksheetFunction.Pi
mhu = 398600
vpp = (mhu / Math.Sqr(mhu * a * (1 - e ^ 2))) * (-Math.Sin(ta * Pi / 180))
fn1 = 2 * vpp
End Function
View 9 Replies
View Related
Jan 25, 2008
I have a spreadsheet that uses a function defined in VBA code that is housed in an add-in which is stored in the default location (C:Documents And Settings{user_name}Application DataMicrosoftAdd-Ins). To share this spreadsheet, I first install the add-in on the other person's computer. Then, when the other person opens the spreadsheet, the function doesn't work because the function call on the worksheet contains the whole path of the add-in (and the path contains my {user_name} rather than the other person's). So, the immediate solution is to just delete the path of the add-in from the function call (so that only the name of the function remains, plus the arguments of course). But this is quite inconvenient given the number of times the function appears in the worksheet, and it's not practical as we continue to share files like this.
My question is: is there a way to make sure that the 'C:Documents...' never gets added to the function call in the first place? I mean, why doesn't Excel just try to run the function locally before it adds that path that refers to a separate computer? I do realize that there are other methods of sharing VBA code. For example, I know that I could include the code in the specific spreadsheet that I'm trying to share, and this would work. But, that's not the answer I'm looking for (for one, the function refers to a very large amount of data stored in the add-in, and there are other reasons that make the aforementioned method impractical). I'm wondering if I'm missing something, and if there is a way to stop Excel from tacking on the path string to the function call, so that the function would just work on any computer as long as they have the add-in installed?
View 9 Replies
View Related
May 25, 2006
I have written a public function inside a class module. Is there anyway I could call the public function in Excel? What is the exact syntax to do it?
View 5 Replies
View Related
Aug 2, 2006
I am working with several columns of data, and each column's range is stored in a range array. My program needs to use a specififc combination of the columns as the second argument sent to the regress function. In certain cases, the columns I am referencing are not contiguous, so the function won't work. Here's an example:
Suppose we're working with 3 independent variables (a simple case) whose data is stored in three contiguous columns named x1, x2, and x3. The program I've written will call the regress function 6 times: once using x1, once using x2, once using x3, once using the union of x1 and x2, once using the union of x1 and x3, once using the union of x2 and x3, and once using the union of x1, x2, and x3. Suppose the range for x1 is d5:d18, the range for x2 is e5:e18, and the range for x3 is f5:f18. Also, each union of ranges is stored as one element in an array called Combos, which is sent to the regress function.
The regress function returns an error when prompted to calculate regression stats for columns x1 and x3 because they aren't contiguous. I need to know if there is some sort of way around this. write a procedure that will detect if the ranges being input to the regress function are contiguous, and if they are not, copy them to a new worksheet so they will be contiguous and then send the new range references to the regress function for calculation purposes? Is there an easier way around this issue? If I can't get this to work, my entire program is wasted.
View 3 Replies
View Related
Dec 18, 2006
i'm trying to call a function from another one, i'm getting this error 'Compiler error: = expected' but i don't know the reason, the functions simply take some values an store them in an here is the
Dim productos(19, 3) As String
Sub agregarProducto(ByVal descripcion As String, ByVal modelo As String, _
ByVal precio As String, ByVal unidad As String)
Dim r As Integer
For r = 0 To 19
If productos(r, 0) = "" Then
productos(r, 0) = descripcion
productos(r, 1) = modelo
productos(r, 2) = precio
productos(r, 3) = unidad
End If
Next
End Sub
Sub agregarProductoTelas()
Dim descripcion, modelo, precio, unidad As String
If Selection.Column = 1 Then
descripcion = Selection. Offset(0, 0).Value
modelo = Selection.Offset(0, 0).Value
precio = Selection.Offset(0, 3).Value
unidad = Selection.Offset(0, 2).Value
agregarProducto(descripcion, modelo, precio, unidad) 'error happens right here
MsgBox (descripcion)...
View 3 Replies
View Related
Sep 7, 2013
In an Excel sheet, in A1 I have written B1 and in A2 I have written D20.
I have a data panel in B1:D20.
I would like to call a vba function my_Fun that acts on my data panel and returns a double in cell A3.
Instead of writing my_Fun(B1:D20) in A3 I would like to be able to write my_Fun(A1:A2), where the entries in A1 and A2 are called, rather than A1 and A2 being used to define the range themselves.
View 5 Replies
View Related
Jan 10, 2013
I try to call a function/procedur in an Excel2010-AddIn (.xlam file) from another Excel2010-AddIn (.xlam file):
Code:
Application.Run (filename.xlam!functionname)
Both AddIn-files are in the same directory (the default AddIn directory).
This call causes the error message "runtime error 91: object variable or with block variable not set".
This problems came up with providing an existing Excel2003-AddIn for Excel 2010.
The same code runs in following cases:
1. call a function in an Excel2003-AddIn (.xla) from another Excel2003-AddIn (.xla)
2. call a function in an Excel2003-AddIn (.xla) from a Excel2010-AddIn (.xlam)
View 1 Replies
View Related
Aug 14, 2014
See attached file for my sample workbook.
What I would want to happen is that I want to be able to use Vlook up by using the Value of the Textbox1 in the Info form and place the vlookup result in the fnameresult box.
Data are in Nickname sheet A:B
test template.xlsm
View 4 Replies
View Related
Feb 27, 2014
I would like to have an Index & match function in my userform.
I know that i must use it like this Application.WorksheetFunction but i am not able to do it.
User select a code and then in next text boxes, description and price should appear.
View 6 Replies
View Related
