I am using the following code to open a userform. Once the userform is open, the user has the option to choose a continue button or a cancel button. If the user is choosing continue, the code is not working (it always skips to the "else" portion of my "if" statement. I can't figure out why it is doing this.
View 4 RepliesI have a userform that is called within a sub in module. I declared a public string, "divisonb", in the module. When the userform's ok button is clicked, I define the public string through a "select case" method.
At the end of the private sub for the ok button click I have this:
[Code].....
a message box comes up with the correct string for divisonb. After the sub ends and it returns to the module I have the following:
[Code] ....
When this message box pops up, it is blank. Somehow, divisonb was redifined as blank within that 2 lines of code. All my other public strings are returned to the module with their correct values.
I have the following code in 'ThisWorkbook':
VB:
Private Sub Workbook_Open()
Call Meetdata
End Sub
It calls this macro which is in a standard module:
VB:
Public firstvariablename As String
Public secondvariablename As String
Sub Meetdata()
regionname = InputBox("Enter the name of the Region.", "Region Name: North, South, East, West")
meetdate = InputBox("Enter the date of the Meet.", "Date of Meet")
End Sub
This set-up should make the variables available to all the other macros in the workbook. I have two other macros that need to use the values stored in these two variables that are entered into the two 'InputBox' statements. These two macros are in the same module and follow the 'Meetdata' sub. When I run the first macro, it recognizes the variables. When I run the second macro for some reason the variables are not recognized. To test the values returned by the variables, I placed the following code at the end of the first macro and again at the beginning of the second macro .
VB:
MsgBox(firstvariablename & " " & secondvariablename)
At the end of the first macro, the MsgBox returns the correct values stored in the variables which means that when the macro completes its run, the variables still hold their values. When I run the second macro with the MsgBox at the beginning, the Msgbox returns a blank. Somehow, the variables have been re-set to a null value. I can't figure out why the variables have been re-set to null. [URL]
Dim lngrow As Long.
i want the above variable to hold the value of endxlup . so for example :
lngrow = Cells(.Rows.Count, "A").End(xlUp)
Why doesnt it hold the # of the last cell with data in column A ?
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.
I have two open workbooks, each having a single worksheet which includes a command button. The VBA code for each button performs tasks on its worksheet, then activates the other (inactive) workbook. I want to set the value of a variable in the button_click code in Workbook1 before activating Workbook2, and then use the value of the variable in the button_click code in Workbook2.
Declaring the variable as 'Public' doesn't work because the variable scope remains within its own workbook project. I must use two workbooks rather than two worksheets in one workbook.
I have some code in which I need to pass the value of "j" from one module to another. "j" is declared in Sheet1 (Data) under the Microsoft Excel Objects.
View 8 Replies View RelatedThis might be a dumb question but is it possible to pass a boolean variable as a parameter to another function?
I have the following code that produces some compile error ("expected ="):
I am trying to pass a variable called "Filter" from a Private Sub to a Module but keeping coming up with a zero value in the Module. I tried to make the variable Global but that didn't seem to work.
Private Sub Code as follows:
I have a spreadsheet that has several buttons on that run code using the cells around the code for parts of the data. eg
ColumnA1 ColumnB1 ColumnC1 Button1
ColumnA2 ColumnB2 ColumnC2 Button2
I want the buttons to run a seperate piece of code to start, but then all buttons run a similar piece of code at the end. I am hoping to be able to pass a variable from the first piece of code each button runs into the section of code that all share (which I have done as a seperate macro)
I've been trying to pass a variable from the OnChange- event on one sheet to a macro located in module one, but it seems I cant (or havent figured out how anyway). Anyone knows how to do it? The code here give me the error "RefreshSetup(WhatSheet) cannot be found"
Alternativly, maybe someone can come up with a better solution on my entire problem. 3 sheets with webquery. All should be handled in the same way (copy-pasting only), but source and destination-sheet differs. My big problem is if two queries are done at the same time, they conflict with eachother, and data from one query is copied on to the wrong sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Running Then
RunWhen = Now + TimeSerial(0, 0, 1)
WhatSheet = "HtmlTmp"
Application.OnTime RunWhen, "Module1.RefreshSetup(WhatSheet)"
Else
RunWhen = Now + TimeSerial(0, 0, 3)
WhatSheet = "HtmlTmp"
Application.OnTime RunWhen, "Module1.RefreshSetup(WhatSheet)"
End If
End Sub
Module1:................
I'm trying to pass a vector with in Strings (words) to a VBA variable, but something is not working.
That's how I'm writing: ...
I am having a problem with a run time 13 error on a variable assignment. The variable is being assigned a value from a cell that contains a formula, and I am suspicous that perhaps the mismatch is coming thru this. The de-bugger stops on:
Area = ActiveSheet.Cells(count + 41, 8). Here is the complete code (with all my poor coding skills!)
Sub Reservoir()
Dim Inflow As Single
Dim Withdrawal As Single
Dim Evap As Single
Dim Area As Single
Dim Spill As Single
Dim Stor_1 As Single
Dim Stor_2 As Single
Dim count As Integer
Dim max As Integer
max = ActiveSheet.Cells(9, 4)
For count = 1 To max * 12
Stor_1 = ActiveSheet.Cells(count + 40, 11)
Inflow = ActiveSheet.Cells(count + 41, 4)
Withdrawal = ActiveSheet.Cells(count + 41, 6)..........................
I currently have a userform which inputs data into two cells, the third then multiplies these two cells together, which is all honky dory. It works, 2 x 2 = 4.
But when i put 0.6 for example it really doesnt work. 0.6 x 25 = 25. WRONG. My code in the macro is fairly long winded but will paste anyway.
If ComboBox2.Text = "Pounds (£)" Then
Sheets("Claims").Range("G16") = TextBox2.Text
Else
Sheets("Claims").Range("F16") = TextBox2.Text
End If
Sheets("Claims").Range("B16") = ComboBox1.Text
Sheets("Claims").Range("C16") = TextBox1.Text
Sheets("Claims").Range("B16") = TextBox3.Text
Sheets("Claims").Range("D16") = TextBox4.Text
Sheets("Claims").Range("E16") = TextBox5.Text
Dim a As Long
Dim b As Long
Dim answer As Long
a = Sheets("Claims").[E16]
b = Sheets("Claims").[F16]
answer = (a * b)
Sheets("Claims").[G16] = answer
I'm trying to pass a variable from Word to Excel. Basically I have a Word document with a plain text content control in it. I'll have users populate this field. I know how to create a reference to that content contol in Word VBA that'll tell me what's in that content control (eg.
Code:
MyWordVar = ActiveDocument.ContentControls(1).Range.Text
).
What I can't figure out is how to pass the value of "MyWordVar" to a variable in Excel.
I have the following codes in my spreadsheet, they are pretty much the same and both used to work perfectly. However, now the first code returns a Type Mismatch '13' on line beginning mynum= and the second one works perfectly.
Sub addtasks()
Application.DisplayAlerts = False
myrow = Cells. Find(" Total P&C Estimate").Row - 3
mycell = Cells(myrow, 2)
mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
.EntireRow.Copy
.EntireRow.insert Shift:=xlDown
End With
Application.CutCopyMode = False
Cells(myrow + 3, 2) = "Task#" & mynum
Application.DisplayAlerts = True
End Sub
I want to know how to index a drop down list to be used for calculations.
Up until now I was using
Range("B1").Select 'the cell where the drop down list is
ActiveCell.Formula = SiteID 'site id
But for whatever reason it trims a trailing zero from SiteID when setting it in the second line. SiteID is a VBA string.
Indexing it would work, as would preserving the zero so help on either one is appreciated. Auto Merged Post Until 24 Hrs Passes;Correction, I lied. I was using this line to set it.
Range("B1").Select 'the cell where the drop down list is
ActiveCell.FormulaR1C1 = SiteID 'site id
I am trying to eliminate a lot of the global variables from my program by passing the variables to my functions and subs as arguments. I am stuck though when it comes to variables created in userforms.
The program starts with a series of userforms that asks the user for information that will be used throughout the rest of the program. Data is assigned to the variables on the click events. Is it possible, without using global variables, to pass those variables to the rest of the program?
Example:
I have used my Excel Bible as well as MrExcel.com and tek-tips.com to write the current code I have... but it doesnt work! I think I am just missing a small piece, but I dont see what it is.
I have a macro that calls a userform - from the userform, a Customer is chosen. When the "extract" button is clicked on in the userform, it should pass the customer name to the next macro to do a bunch of stuff - including filtering the data by the customer chosen in the userform.
Here is the code - take a look and see if you can figure out why it doesnt work.
Code for "extract" button (I have tried several differnt variations - this is the current one):
Code: ....
When a user selects Yes from a drop down list in a particular cell a UserForm pops up to request further information.
How can I pass this information to the cells to the right of the "Yes" cell.
E.G. Cell H6 contains Yes, the UserForm info is to be passed to I6 and J6.
This needs to happen on each row everytime the user selects "Yes" -
Yes in H7, Userform info to I7 and J7 etc etc.
I have several non-modal userforms in my App, some of them have date-fields that require manual entry typing of dd/mm/yy etc (No single userform has more than one date-box in it, this I think may be pivotally useful)
Now the Userform 'Calendar' that is built on the class of the same (cCalendar) name, has the write value line 'ActiveCell.value = theCal.value'
I'm looking to change this to refer to the correct userform.Textbox value, depending on which form is open.
I would imagine I could simply have a global string, whose value is set (or re-set) whenever a Userform is initialized (some sort of 'ActiveUF.value = Me.Name), where I get lost is referring to the components by name, so as to have a case statement by where I go:
Code:
Private Sub theCal_AfterUpdate()
Select Case ActiveUF
Case "AddForm"
application.vbe.components("AddForm").controls("AddFormDatePicker").value = theCal.value
Case "EditForm"
'.... etc
end select
end sub
better way of doing this (instead of passing around the userform name as a variable) - or proper syntax for referring to controls outside of the 'active' userform (but an open userform nonetheless)?
Every time I have to do this particular thing with userforms, I completely forget how, and the object browser always leads me on an infinite loop of Application.vbe.activevbproject.vbcomponents.vbe.active....
PS - there may be one slight complication to the process - one of the forms, has a 2-tab page in it, each page having similar (but named differently) fields. So I may need to be able to throw in 'Activepage' or whatnot
In order to deal with the multitude of screen resolutions that can be found at our work, I have got a module to check the current screen resolution on loading the workbook. I then have set up 3 different sized userforms, all containing the same coding, and based on the displayed screen resolution one of these is open (the userforms are named Customer1, Customer2, Customer3).
The displayed userform then has a button to show another userform (Customer_Details), and this form requires some referencing back to the previous form for some of it's information eg,
customer_details.textbox1.text = customer(1, 2 or 3).textbox1.text
customerdetails.combobox1.list = customer(1, 2 or 3).combobox.list
way for me reference the specific "Customer" form that has been opened, without calling it by name?
is there a way how to make text in a user form created in VBA dependable on a value of some variable? In another words: I want the userform to pop up at some point and I want the text inside of userform to vary depending on a variable which can take on four values.
View 9 Replies View RelatedI got some codes defined as below and I want use a userform for and checking the process. If ı could use variable "i " in userform, my codes would work.
Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("E3:P36")
[Code] ......
Hi, i'm having a terrible problem (at least for me).
I want to use a var from a UserForm to use it in calculation method in a Module.
How can i refer to that variable in the Userform in the Module code?
I have code in a userform that shows another userform with a text box. When the user types text there and presses OK, I want that userform to close (which it does fine), and return back to the original userform (which it does fine), but I want the typed text to be stored in a variable that the code in the original userform can use.
I tried declaring a public variable, "Rresponse1", in each userform's procedure, but that doesn't work. I test for the content of the variable afterward in the original userform and it is empty.
In the original userform I have:
SaveList.Show '(this is the second userform)
MsgBox "This is response1: " & Response1 '(to test if the variable is being passed)
In the userform SaveList, in the Private Sub CommandButton1_Click() procedure, I have:
Response1 = SaveList.ListName.Text
Application.ScreenUpdating = True
MsgBox Response1 '(to test the variable, here it shows the text fine)
Unload SaveList
How do I get the Response1 variable to keep its content when control is passed back to the original userform?
I have a macro that calls a User Form using:
Code:
Update_Too.Show
The form has a drop down list menu and the user needs to select a month. I store que choosen value in a variable named "OPT", using this code:
Code:
OPT = Me.Months_List.Value
I declared this variable at the start of the macro like this:
Code:
Dim OPT As String
But it doesn't seem to be working. I am not declaring the variable in the user form,
I maked a userform who use global parameters (to let the user decide where write things) and later i need to use this parameter in a module (who is the main program). I try to resolve this problem put this global parameters like global parameters in the module and later in the both sides (in the module and in the useform), but it can't work. How i can resolve that?, i.e., How i can use a global parameter in a userform and the same global parameter in the modulo with the same data?
View 6 Replies View RelatedI was just trying to work around a problem with multiple UserForms in project. I have assigned Object variables oUserForm1 and oUserForm2 to represent UserForms of specific names.
VB:
Dim oUserForm1 As Object
Dim oUserForm2 As Object
Set oUserForm1 = VBA.UserForms.Add("Data" & CStr(X))
oUserForm1.Y = Y
[Code] .....
When oUserForm1 shows new data are inserted and another macro runs with a line to hide the opened UserForm.
In my project manager this user form name is i.e. Data1 (for x = 1), but the syntax Data1.Hide returns error. After that line it ask me to close the TOP most modal UserForm.
But When I use Me.Hide all works well.
My question is: Does VBA not see the name of the userform ("Data1") because I have used the oUserForm1 variable to give it a focus to it?
I am trying to pass two variables RARD and CARD from Sheet1 'change selection' subroutine to the 'Set ARD Command button click' subroutine. The variable value remain empty and it gives me a 1004 error when I try to execute this line of code:
[Code] ....
What I am trying to do is put the text value 'ARD' in the cell on sheet1 which was the original cell I clicked on to open the userform2. To do this I click on the 'Set ARD' button on the userform2 which is then suppose to hide the userform2 and put the 'ARD' text in the selected cell on Sheet1.
I have attached the workbook for review : Therapy Tracker - Tester V2.10 - deleted logo.xlsm‎