I want to display a string to users. Normally I would do that using message boxes. But that would not give me enough control over the font size, and I want to display using font size 48.
I could have used global variables, but that is not a good coding way. The other way of doing that is using property procedures. I could pass a string variable to a Label control on the form and then could format it.
When working in an userform, and you call another sub routine within the userform, will all variables passed automatically be byref (no way to do byval?)?
View 3 Replies View RelatedI have been trying to use a multi-select listbox as the argument for a subroutine. For some reason, I keep getting a run time error, type mismatch. I'm baffled because when I define the specific listbox, it works fine, but when I attempt to pass the listbox I get an error.
Public Sub OtherDirectTotal(Expense As ComboBox, CostCat As ComboBox, _
Cost As TextBox, Years As Msforms.listbox)
Dim IDC As Double
Dim IDCt As Double
IDC = Val(UserForm2.txtIDC) / 100
If Years.Selected(0) = True Then
If Expense = "Sponsored" Then
If UserForm2.ChkODC = False Then
Select Case CostCat......................
Have a file lets say is named
sample.xls
Does anyone know how to retrieve the name of the file in VB?
just the name and put it in a string?
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 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
I have a macro that when it runs presents a form that gives the user an option to skip the calculations or continue. If they click Skip then it continues the 'Next CE' . My problem is I can't get the Boolean value to pass from the form to the macro. Here is what I have
The main macro
Sub Dividend_MF()
For Each sh In ActiveWorkbook.Worksheets( Array("G&I", "Growth"))
For Each ce In sh. Range("a5:a" & sh.Range("a65536").End(xlUp).Row)
If Not IsEmpty(ce) And Not (IsError(ce.Offset(0, 56))) Then
If ce.Offset(0, 56) = "X" Then
how is the best method to link up forms to "talk" to my modules? (aka set variables inside my module script)
the way i understand it with my C++ background, it seems like separate forms and modules act much like private declarations do inside class calls.
I know that in C++ i can declare a friend class to grant access to communication between classes... and i'm wondering if such a procedure is possible in VBA as well...
or to break it down for those not familiar with C++, quite simply, is there a method of code i can implement to have different user forms and modules to talk to eachother? as it seems that any variable declaration is all inclusive to that one object.
The only way i can think of passing variables is by having my code create a worksheet, paste variables one by one into cells, bounce to the destination module, and import those declarations one by one through the cell contents, and remove the sheet after use...
I know i can do it that way, but i really dont want to as that is pretty darn sloppy, and a mickey mouse way to tie my program together.
My question is about passing arguments to UserForm procedures. I've created a Main procedure. Main calls a sub procedure and returns an array. Obviously Main can now use the array. But then Main calls a sub procedure to open my userform.
All of my userform sub procedures need to use the array I created in Main. Do I have to pass the array into all of my userform procedures? Into the UserForm_Show procedure? And then again into UserForm_Initialize? And then again into ComboBox_Change? I don't understand how to give my UserForm procedures the array from my Main procedure.
I have a userform that should return a global varable but when the global varable is used back in the program...it is 0 and not the number in the userform.
The varable I speak of is ForcedCurrent
Global declairation
Private Type TempData
LotNumber() As String
Site() As String
Macro() As String
ChainName() As String
ResistancePerContact() As Double
Index As Integer
End Type
Private Type Lot
Product As String
ParaOrDeft As String
ThermalOrStress As String
Hours() As String
Data() As TempData
Index As Integer
End Type
Dim LotData As Lot
Dim TestNameArray() As String
Dim NumberOfContacts() As Double
Dim ReedholmUnits() As String
Dim ForcedCurrent As Double ...
The first goes through a directory and opens all the files.. after it opens a given file it goes off into a sub-routine to process the data in that file.
I am trying to create a counter in the first sub-routine and then pass that value into the second sub-routine to tell it to put the values out on the next row down.. so the first time through it puts the values out on row 1, next time it puts them out on row 2, etc.
The following line comes up with an error message, where downoffst is an integer variable that has already been defined.
Set sumif1stRange = Range("c5:c[downoffst]")
Do you know how to debug this?
I am trying to pass a string to a function requiring a ParamArray. See below
Code:
Declare Function LibFunc1 Lib "FuncAddin" (ByVal vtSheetName, ParamArray Mylist() As Variant) As Long
Sub testsub()
Year = 2013
Period = December
MyStr = "Year#" & MyYear & """", "Period#" & MyPeriod & """"
LibFunc1("Sheet1", MyStr) 'how to declare string so it can be passed as ParaArray?
End Sub
I am calling a sub that I want to do a SaveCopyAs using a variable as the file name but can't get it to work. The file name displays properly in a message box but when I do a SaveCopyAs it does nothing.
This code works:
I want to work on a range
range("A1:B100")
The number 100 (in B100) comes from another varibale M
Now how do I write the range so that I don't have to write 100. I want to pass on the variable in the range.
the code should look somewhat like
Range("A1:BM").
I don't know how to pass this variable M onto the range.
It is possible to define a variable in a MS Word macro and send it to an Excel spreadsheet?
View 9 Replies View RelatedI created the following sub to signal when a macro in Module 1 is complete:
Public Sub Done()
Dim complete As Boolean
complete = True
End Sub
I placed this just before the end sub in the macro for which I am trying to detect that it has finished executing:
Call Done
End Sub
In the Sheet 1 Module, the code fails at the statement:
If complete = True Then
The error returned is "Variable not defined." All three subs are declared as Public. Why does the Sheet1 sub not recognize the variable "complete" from the Module 1 macro?
How do I reference a spin button from a variable that I set.
e.g
Dim objSpn As Object
Dim y As Integer
Set objSpn = ActiveSheet.Shapes("spnWCDate")
y = objSpn.Value
This doesn't work, but hope it explains what I want. It's so I dont have to write the whole object name each time I reference it.
ADDED: I've just noticed that this doesn't work even i do reference the object with the full name. I copied this from a forms spin control that I was using, why can't i store the value of the spin button in a variable?
how to specify a file extension type for a browseforfolder part of what i've been doing.
It's not calling correctly as i'm not sure how to feed the inputData string back into the main.
Obviously there's probably better ways of doing this, but can my way be corrected easily?
I've been looking into ByVal and ByRef, but have only seen examples goin from main to the sub and not the other way.
Code:
Sub Main()
Dim strFolderName As String
Dim strFolderTest As String
Dim FRCntrHiLim As Variant
MsgBox "before calling inputbox"
Call InputBoxTest(inputData) '
is it possible to assign the formula to a variable ? here i am trying to get only the filename excluding the path and assign it to a string variable. but its not working check "strr1" line.
Sub TestReadDataFromWorkbook()
' fills data from a closed workbook in at the active cell
Dim tArray As Variant, r As Long, c As Long
Dim i As Integer
[Code]....
How do you pass a variable value in Visual Basic?
I have a variable called LastRow
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Would like to pass the value in my Do While loop
Option Explicit
Sub IncValue()
Dim MyCell As Range
Dim LastRow As Long
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Do While MyCell
I have a spreadsheet with about 30 charts on it that I would like to attach a macro to which opens the chart in a form. I've figured out how to do this last part, but am stuck on how to pass a variable to the form code which tells excel which chart to copy. Essentially what I'm doing creating a macro for each chart which would run when that chart is clicked on. This macro is identical for each chart except for the name of the chart being passed.
Sub Chart1_click()
ShowChart "Chart 1"
End Sub
Sub ShowChart(c As String)
frmChart.Show
End Sub
In the UserForm_Initialize code located in the userform module, I'd like to call the code which saves and loads the image of the clicked on chart. But here's where I'm stuck. How do I pass c to the code in the form module...e.g, how to pass c to UserForm_Intialize and to ChartZoom? Here's what I have so far...
Private Sub UserForm_Initialize(c As String)
ChartZoom c
End Sub
Private Sub ChartZoom(c As String)
frmChart.Show
Dim Cht As Chart
Set Cht = ActiveSheet.ChartObjects(c).chart
Dim CName As String
CName = ThisWorkbook.Path & "cht.gif"
Cht.Export Filename:=CName, FilterName:="GIF"
imgCht.PictureSizeMode = fmPictureSizeModeZoom
imgCht.Picture = LoadPicture(CName)
End Sub
Following statement works for me:
bdcTerm1 = ThisWorkbook. Sheets("ws2"). Range("A1").Value
But instead I want to parse through 50 rows and dynamically get the value instead of using a static Range("A1"). So I am trying to do the following:
For Row = 1 To bdc_rows
bdcTerm1 = ThisWorkbook.Sheets("ws2").Cell(Row, 1).Value
bdcTerm2 = ThisWorkbook.Sheets("ws2").Cell(Row, 2).Value
Next Row
But I get errror.
I have a code below which need some input from user. This input will also be serve as the input of the subroutine which i am going to call. However, i do not know how to go assign this input to the subrountine which i will be calling, can anybody help ?
For example, the "input" variable will also be served as an input in subroutine test2 ...
I have a macro that nicely select the named range that the active cell is in. I want to chain on to that macro a macro that has a parameter a range with that active selection. I dont see how to "take" the active selection on the worksheet from within the macro and pass it to another. I assume I could change the cellInRange macro to return a range, however I dont yet see how to do that.
View 2 Replies View RelatedThis is the code after editing to make it more clear
Public Sub 1()
BookA= activeworkbook. name
BookB=Application.Workbooks.Add
Workbook(BookB).activate
End Sub
Public Sub 2()
BookB=Activeworkbook.name
With BookB. sheets(1)
.range("A1")=BookA.sheets(1).range("B1")
End Sub()
At the end of public sub 1, BookB is the active workbook. What I want to do in public sub 2 is to copy some data from BookA to BookB. Unfortunately, when moving from public sub 1 to public sub 2, BookA needs to be defined again. The code above is the code that I use in my add-ins. I figured out for non add-ins code I can define BookA with thisworkbook.name when BookB is active as I before work with BookA. This does not apply for add-ins as thisworkbook will refer to my add-ins code. Is there anyway of keeping definition of BookA is constant from one public sub to another public sub? This is simplified code. In fact, I can't merge public sub 2 with public sub 1 due to some reason which I don't say it here.
I have come up with the following formula to pull sums of data by month. I am using this because it is inconvenient to use the DSUM formula (I can't put all the criteria together in a simple way):
{=SUM(IF('Sheet1'!$B$2:$B$20="Person1",IF( DATE(YEAR('Sheet1'!$A$2:$A$20),MONTH('Sheet1'!$A$2:$A$20)+1,)=C2,'Sheet1'!$D$2:$D$20,0),0))}
I would like to, in a macro, assign a variable to this result - but cannot figure out the VBA code for it.
This line in my code is causing an invalid qualifer error message:
lngNew = wsNew. Range("B65536").End(xlUp).Row.Offset(1, 0)
Option Explicit
Sub Disney_DumpData()
Dim wbBook As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim rngNew As Range
Dim rngUnit As Range
Dim rngUnitPaste As Range
Dim lngNew As Long
Dim Cnt As Integer
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Set wbBook = ThisWorkbook
Cnt = 0
'Dump To New File
'/Define new workbook
Set wbNew = Application.Workbooks.Add.....................
I am trying to pass information that is filled by user in a userform into an excel sheet. Let's say a user would click on a control button in a userform and Macro would ask him what value to store for the first variable. If user clicks one more time then Macro would identify that it was a second click and ask what value to set for a second variable. It is easy to do with limited number of variables, but is it possible that the variable which stores a number of clicks would become a number for variable to store the value?
1 click - a1 = ..
2 click - a2 = ..
....
n click - an = ..
If not possible - which way to search a solution?
I'm just starting to experiment with passing values between workbooks and between modules and so far I've managed to get it working. However, I now can not run the receiving module independently because of the passed value ? Let me explain ...
I use start/end dates within my modules to create date sensitive reports. I have several files (for different departments) and each file has a module called "AbsenceChecker", I have been working on a way to create a Report Master workbook to run and amalgamate the "AbsenceChecker" module from each department.
Sub RunAllLoaders()
Dim AbsenceStart As Date, AbsenceEnd As Date
Dim PassVar1 As Date, PassVar2 As Date
Dim DateStart As Date, DateEnd As Date
On Error Resume Next
'start of data validation script continued in private sub.
GetValidDates FromDate:=PassVar1, _
ToDate:=PassVar2, _
MinDate:=DateSerial(2007, 1, 1), _
MaxDate:=DateSerial(2007, 12, 31)
Excel.Application.EnableEvents = False
Workbooks.Open Filename:="R:RostersRosterALPHA.xls"
Application.Run "RosterALPHA.xls!AbsenceChecker", PassVar1, PassVar2
End Sub...................