I created in module custom toolbar with a button, see the
Public ctrlGUI As CommandBarControl
Dim cb As CommandBar
On Error Resume Next
On Error Goto 0
Set cb = Application.CommandBars.Add( Name:="Cust", temporary:=True)
.Visible = True
Set ctrlGUI = .Controls.Add(Type:=msoControlButton, temporary:=True)
.FaceId = 3205
.Style = msoButtonIcon .............
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 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
I defined two custom buttons in Workbook_Open() function:
Private Sub Workbook_Open() Dim cb As CommandBar Dim ctrlInit As CommandBarControl Dim ctrlComp As CommandBarControl On Error Resume Next Application.CommandBars("Project").Delete On Error Goto 0 Set cb = Application.CommandBars.Add( Name:="Project", temporary:=True) With cb .Visible = True Set ctrlInit = .Controls.Add(Type:=msoControlButton, temporary:=True) With ctrlInit .FaceId = 68 .Style = msoButtonIcon .Caption = "Retrieve Template Types"............
In Module1 I must to turn ".enable" properties to Turn, but Excel don't recognize these variables there.
Hopefully this isn't too vague, but almost every project I've worked on so far has not recognized Public variables in all modules. I've constantly been searching for exceptions to this rule that could be contributing to this issue but haven't been successful.
I have read a million times that declaring a variable as Public makes it visible to all modules and preserves the value throughout. I've also read that if you edit code or reset your project, the values may be lost. I also know that if you try to use a variable before you give it a value (or for object variables set it = to something), it doesn't have a value yet. But all this applied, I'm still not getting Public variables to be consistently recognized or stay with the value I want them to have throughout the lifetime of my project (while the workbook is open)
My specific current issue (one of many so far) is that I have 4 command buttons within a worksheet object. Each triggers a different group of procedures, some of these are user forms. In the first button, I create two worksheets one to contain the current fiscal period "Options" the other for "Summary" of all the totals etc... (all financial data). In this first userform, triggered by the button, I allow the user to name these worksheets: like Summer2007Options or Summer2007Summary and I declare these publically as object variables OptSheet and SumSheet. These variable names and sheets are recognized for the next 2 buttons but now that I'm on the fourth, I have object variable not set errors when I try to refer to this variable.
I have a code that runs when I open the workbook. It asks if this is a new quote, if it is it should then open userform1. The problem is that sometimes when I click yes I get a "Path/File access error" popup, and then a VBA error box with "Run-time error '75' Could not find the specified object".
When i debug, it highlights the line "userform1.show"
IF i end, and then run it from within VBA it will then run fine.
Sometimes when I open the workbook it will just crash excel, and when I reopen it, it runs fine.
Simple code -
answer = MsgBox("Is this a new quote?", vbYesNo) If answer = vbNo Then Else userform1.show
I would like to save a module level variable but do not know how to do it. I have tried using the public key word. If I put it in the sub, I get an error when it tries to compile the sub. If I put it outside of a sub, it just doesn't work. Surely this is something easy.
The attached file contains a simplified version of a more complex macro in which a sub routine will be called a number of times to change the colours of cells in different ranges. However, an error is triggered because the a variable and range are not defined in the subroutine although they are in the main part of the macro.
I know I am missing something obvious here, but I'd appreciate any help in knowing how I can define the ranges in the main procedure and then call the subroutine to change values in the different ranges.
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 _ & "email@example.com or just the Staff ID.", _ Title:="Recepient", Default:=Range("I" & vaRow).Value) Loop While vaRecipient = ""
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.
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 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?
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.
I am using Excel 2010 and late binding to generate reports. The sheets are blank and unused. I have the UserForm being opened on Workbook_Open and the actual VBA for doing the work in a separate macro in the workbook. All the posts I have found are dealing with accessing cells from the UserForm and I haven't found any dealing with connecting a macro to the UserForm output.
My question is this: I want to return from the UserForm the flags of which reports to run to the (unopened) macro containing the code to do so. How do I open the macro and pass the variables to it?
The UserForm is creatively called "SelectionForm" and the macro is called "DailyFTP" with a Main sub as the entry. I know I can use the code under to capture the radio button and checkbox choices. I have other variables like the names of files set as global variables in the DailyFTP macro. The macro doesn't exist until SelectionForm opens it, so they must be set after the macro is in memory.
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:
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
After all the awesome macros I've obtained with the help of all of you, I now have over 30 macros, each in its own module. I have tried without success to re-name the modules with no luck. How is everyone organizing these?