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.
stretch" the userform to fit the height and width fo the screen. I dont want to simply change the height and width of the userform, i want the controls within the userform to stretch with the userform so that it looks the same, just fullscreen. this is what ive found by googling:
VB: With Application .WindowState = xlMaximized Zoom = Int(.Width / Me.Width * 100) Width = .Width Height = .Height End With
however, this just stretches it to match the width of my screen, but doestn consider the height, so that if i have a widescreen vs a square screen it will cut off the bottom of my userform. It would be great if there was a zoom type featur that zooms the vertical and horizontal dimensions seperately... more like a stretch than a zoom.
I have a UserForm that I want to use as a Password screen The form has a label, CommandButton and TextBox.
When I run the UserForm, IF you click OK without entering the Password, you still gain access to the Workbook! IF you type in the wrong Password you still gain access to the Workbook and NO "Invalid password!" message pops up.
What I would like to happen IF the CommandButton is clicked without entering the Password, is for the Workbook to close, and IF the Password is correct, I want to load Useform2
I am using the following code provided by rorya, that I got from here..... http://www.mrexcel.com/forum/showthr...light=password
[CODE] Option Explicit Private Sub CommandButton1_Click() Me.Hide End Sub Private Sub UserForm_Initialize() Me.TextBox1.PasswordChar = "*" End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Me.Hide End Sub
Sub Mycode() Dim frm As UserForm3 Set frm = New UserForm3 frm.Show If frm.TextBox1.Text = "Password" Then ' run your code Else MsgBox "Invalid password!" End If Unload frm End Sub [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 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?
I 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 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 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
is there a way i can make my user form in full screen? i already drag it to it's full screen but i still can see the blue tool bar with X (close) button i also want to run the macro when i open the file i want it to run automatically with full screen....
I'm having trouble centering my UserForms on the screen. I have tried all the options on the StartUpPosition in the Properties windows and the closest I can get is vertically centered but on the far left. The annoying thing is that it was working (both vertically and horizontally centered) but when I changed the defaults of the option buttons they moved to the position I described. I have two monitors (but excel is maximised on one monitor only) could this have something to do with my problem? If so why did it work before?
I currently have a VB macro written where under certain conditions user forms are shown for the user to select options, display messages, etc. When any of these forms are on the screen, and someone drags them, the menu image is 'dragged' across the screen. Is there a way from preventing this from happening?
Some time ago (not sure how long ago) I created a splash screen that comes up when I first load a particular workbook. There was only one worksheet in the workbook and at that time the data in the worksheet was not so relevant and hardly ever used. That workbook name is "Employees" and the worksheet name is "EmpData". Subsequently, the worksheet ("EmpData") in the workbook "Employees" has became relevant to a new workbook named "Payroll" I created. This workbook is used all the time. It had four different active worksheets.
I realized last week that the data in "EmpData" was very relevant to the work in the "Payroll" workbook so I copied "EmpData" worksheet in it's entirety to the "Payroll" workbook as a fifth worksheet using the same name as before, "EmpData". Now each time I open the "payroll" workbook the splash screen shows up and hangs around for several seconds or longer. It has become quite irritating and I want to delete it.
I'm launching a main form from a menu I've added. I want to make the form modal. From that form, the user can launch several other forms (that I also want to be modal). If any of the forms are modal, then the Excel sheet that is visible in the background does not refresh. If the form is moved at all it creates a dragging pattern on the screen and hides the spreadsheet. That's not so bad, except when I get to either a second or third level form and start closing them. They're still visible even though the previous level form is now on top. Most of the forms get bigger the deeper you go into the data entry process. So, as you close them and move back up the chain, it gets very confusing. If I simply make the forms modeless, it refreshes fine. I've tried using Application. ScreenUpdating = True and Me.Repaint with no success. I couldn't figure out where to put the Me.Repaint since there isn't a UserForm_Move event.
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.
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?
What i have at the moment is a module that contains code where i call a different module that i use as a procedure.
Code: If Sheet1.Range("C4").Value < Sheet1.Range("A2").Value Then If Sheet1.Range("K4") = "" Then MsgBox "Please check 06:00 tasks not done yet!" Cell = "Range(" & Chr(34) & "F4" & Chr(34) & ")" If Sheet1.Range("C4") + 0.042 < Sheet1.Range("A2") Then Run "EmailProSheet" End If End If End If
EmailProSheet is what i call but now i want to use the variable "Cell" in the procedure as well?
Code: MsgBox Cell Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) On Error Resume Next
As it is now everything is working fine but does not return a value in "Cell" if the procedure is called. Is there another way?