I would like the users to be able to exit the whole module via double clicking the form. When the form is double clicked, the double click event appears and I am wondering how to put code into this procedure which exits the whole module.
View 3 RepliesI am trying to pass a string array into a form. I have added a member string array to the form, and a property to "Let" the array in the the member array.
Private sFormString() As String
Property Let FormString(value() As String)
sFormString = value
End Property
I can pass a string in using a procedure:
Sub StringArrayTest1()
Dim TestString() As String
Dim frmString As FString
but I cannot "modulate" the code, or else I get an internal error (error 51). I.e. this code doesn't work:
Sub StringArrayTest2Mod(TestString() As String, frmString As FString)
frmString.FormString = TestString
End Sub
Sub StringArrayTest2()
Dim TestString() As String
Dim frmString As FString
Set frmString = New FString
Redim TestString(1 To 2)
TestString(1) = "Cat"
TestString(2) = "Dog"
Call StringArrayTest2Mod(TestString, frmString)
End Sub
Does anyone know why this happens? Obviously, in the example code its not an issue, but the application I'm using this for is more complex, and some modulation here would be good.
Is it possible to call a Private Sub from another Module?
View 2 Replies View RelatedI 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 RelatedI 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:
When in a ThisWorkbook macro, I want to call a sub/macro in a Module. Usually you can just do a CALL MACRO1, and it will find it. But my MACRO1 is Private as I don't the users to be able to do a Tools, Macro, Macros and see it. So I don't want to make it a Public macro. So how do I call the private sub/macro from within ThisWorkbook?
Can I proceed the sub/macro's name with the name of the module, kind of like CALL MODULE1.MACRO1? Or do I have to make it public?
I've got a problem referring to a named range in another worksheet.
Private Sub test()
Dim var_StartWeek As String ' 0740 This is the value I make a lookup for
Dim var StartWeekNr As Integer ' 40 and get this as a result
'*** Here is the named range in another worksheet (It doesn't work)
Set rng = ActiveWorkbook.Names("WeekData2").RefersToRange
var_StartWeekNr = WorksheetFunction.HLookup(var_StartWeek, Range("rng.Value"), 4) ' I'm doing something wrong here...
'*** If I have the named range in the same worksheeet it works fine
'var_StartWeekNr = WorksheetFunction.HLookup(var_StartWeek, ActiveSheet.Range("WeekData"), 4)
I have a sheet that has a macro on it, I would like to run that macro programaticaly but Im not able to call it using Call mymacro type of code. The code that calls the macro resides in a module and the macro is in a sheet. Obviously they are not talking, is there a way to run it remotely without moving the macro into the module?
I want to write some code that runs everytime the workbook is initialized. I found out that the code should be placed in the following "Private Sub":
Private Sub Workbook_Open()
'My code
End Sub
I know that this code should be placed in the Private Module of the Workbook! I just can not find where is the Private Module of the Workbook in Excel 2007... =/
I know to exit a sub one uses 'Exit Sub', however I have a sub that is calling a second sub (within the same model), and if a variable is a certain value I want to exit...however, when I use 'Exit Sub' it exits the called sub, but then just continues from the original sub...it's not exiting it too. I can work a work around, but I'm wondering if there is a built in solution to this problem?
View 3 Replies View RelatedHow to delete another excel private module of the Workbook Object (ThisWorkbook) VBA codes of the workshook event.
This code to run from my PERSONAL.XLSB
Sub Macro1()
Windows("Book1.xls").Activate
ActiveWorkbook.Vbproject
Don't know what other codes I need to put after this
End Sub
This code to be deleted via Macro1 above code
Private Sub Workbook_Open()
Call CreateMenu
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub
I'm having trouble calling a defined range within a VLOOKUP function in VBA. If the named range is located on the same sheet within which you are running the macro, everything runs fine and all is well in the world.
However, after I relocated the range to a separate sheet (a 'SourceData' sheet to tidy up the user interface sheet), I was getting the following error message:
Method 'Range' of object '_Worksheet' failed
The name is correctly defined - Range("DaysInYear").Select still picks up the correct selection - it's just the VLOOKUP will no longer function correctly.
Here is part of the macro's
For I = 1 To NumberOfDays
Range("A1").Value = DateAdd("d", -(I - 1), EndDate)
If Application.VLookup(Range("A1"), Range("DaysInYear"), 3, False) = 1 Then
If Application.VLookup(Range("A1"), Range("DaysInYear"), 4, False) = 0 Then
ActualNumber = ActualNumber + 1
End If
End If
Next I
I have two userform combo boxes, the second follows up on the results of the first.
The second Combo Box looks for incomplete fields and provides them in a drop box. If the data the second Combo Box is looking at (This is different data than the first Combo Box is looking at.) is complete it updates the field, if not then it adds it to the drop down list.
My problem is this, if it finds everything updated and nothing is added to the drop-down list, how do I exit? I just want a complete break where all programming ends.
I am in the Initialize subroutine and I have tried Exit Sub and it still brings up the user form.
I a macro (Main) I start a userform (FrmMenu.show).
This userform has a cancel button (CmdCancel). When the cancel button is activated I want the macro (Main) to stop running.
can i remove the exit button from a userform
View 2 Replies View RelatedI'm trying to solve when hitting the Save/Close button on my Userform.
Give the User the option to Save As the toolGive the User the option to either exit or return after Save AsIf Exit - hide all but the first worksheet (in case they open with Macros DisabledClose/Exit
My current issue is when I click the button and choose Exit Yes then the form and workbook close, but Excel is still open. Is there a way to have Excel fully close if no other workbooks are open?
Code:
Private Sub cmbSaveAs_Click()
Dim sFilename
If MsgBox("Save the file?", vbYesNo) = vbYes Then
sFilename = Application.GetSaveAsFilename(fileFilter:="Excel Macro-Enabled Workbook (*.xlsm),*.xlsm")
[Code] .........
i have a userform with label, textbox, ok and cancel buttons.this userform is to prompt users for password.
i use select case for capturing the password entered.
case 1 is correct password
case 2 is blank password which is vbnullstring
case 3 is wrong password where i use case else
when i click on cancel button, it will be detected as case else. cancel=true for my cancel button wont work.
i think the logic is to put an IF statement before SELECT to capture if the cancel button is clicked.
I would like to call upon a function until a certain criterion is fullfilled. Then, I would like to have the result returned to me and exit (all) open functions. For value1 = 1 and value2 = 10 I expect value1*value2 = 100. Instead, the routine returnz zero. What is the logical flaw in the code below.
Function testfunction(value1, value2)
If value1 = value2 Then
'Calculating the difference
testfunction = value1 * value2
Exit Function
ElseIf value1 < value2 Then
value1 = value1 + 1
Call testfunction(matrix1, matrix2)
End If
End Function
I have created an Excel Add-in to create the Text file. I want to create the text file through select sheet from userform.
View 9 Replies View RelatedI have been trying many different commands to run a module from a userform, but nothing is working. I'm sure it's possible, right?
Basically, I'm trying to run modules based on a selection made via a listbox.
I 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] ......
I have a Sub() in a module that sends an email to address that it retrieves from a spreadsheet.
See portion of code below
HTML Code:
Do
vaRecipient = Application.InputBox( _
Prompt:="Please add the name of the recipient such as:" & vbCrLf _
& "excel@microsoft.com or just the Staff ID.", _
Title:="Recepient", Default:=Range("I" & vaRow).Value)
Loop While vaRecipient = ""
[Code] .......
This is basically showing 3 popup boxes one after the other to verify each address,
Is it possible to either have it show an input box with 3 fields or ( what I'm currently trying) is to show a Userform . I have tried Userform12.show but it isnt working.
if its possible to have the VBA object on one screen and the VBA code on another?
I can have the spreadsheet on one and the editor on another but carnt get it to work with the objects and code.
I have a project with many textboxes on different forms. On some of the textbox_exits on some of the forms, I call a standard module that checks what the user has input. If the user needs to change the input, a msgbox appears to inform the user the info needs to be changed.
I have tried to use ControlSource, Name etc., But I cannot seem to setfocus back onto which ever textbox the input needs to be changed. I think it has something to do wih the _exit event, but not sure.
I have created a search all sheets function in VB in excel in a Module. All results are populated to a listbox in a userform.
What I want to do is, as soon as I select 1st result in the listbox, it must Jump to cell where the result was found.
If need be, I can mail you the excel sheet.
i have a userform where when i initialize i load my combo box with a range
I have a variable which = combo box value
Sub cmd click()
myval = cbo.value
unload me
End sub
How can i use or pass this variable to my standard module i.e
Sub test()
myevent = myval
end sub
I am trying to use some code similar to what i have used in one of my Modules in a Userform (The module calls this userform)
I have declared a few variables in the module:
Code:
Public LastRow As Long
Public TabLen() As Integer
Public AHUArray() As String
Public ArrayDim As Integer
and i want to try and use them in a userform (i call the userform through an bit of code that usures that the other subs that assign values to the variables have been used so there should be values in the variables??i think?)
i get to this line in my userform
Code:
TabPos = TabPos + TabLen(i)
this is the first line that requires a value to be in the array and it errors: Subscript out of range
I just want a few variable that are used in a module to be available in other modules and userforms.
EDIT:the (i) is decalred in this sub within the userform. A thought occurs, the sub in the userform is a private sub, might that effect it?Also, explain Sub vs Private Sub vs Public Sub?, im new to this! the sub that fils the arrays in the module is just Sub SubName_Click()
I have a splash screen (user form) which displays fo a number of seconds. The UserForm_Activate routine uses Application.OnTime to run the KillSplash subroutine, which is defined in Module1. Thing is, I would like to move the "KillSplash" routine to the UserForm module. What should be the full name of KillSplash I need to pass to OnTime? What "trick" do I use to make "KillSplash" visible to Application.OnTime?
View 9 Replies View RelatedI created in module custom toolbar with a button, see the
Option Explicit
Public ctrlGUI As CommandBarControl
Sub CreateToolbar()
Dim cb As CommandBar
On Error Resume Next
Application.CommandBars("Cust").Delete
On Error Goto 0
Set cb = Application.CommandBars.Add( Name:="Cust", temporary:=True)
With cb
.Visible = True
Set ctrlGUI = .Controls.Add(Type:=msoControlButton, temporary:=True)
With ctrlGUI
.FaceId = 3205
.Style = msoButtonIcon .............
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