Add Shapes To UserForm Via Code
Nov 25, 2006How do i draw shapes on the fly on forms?
View 4 Replies
ADVERTISEMENT
Code To Delete Shapes But Not Charts
Nov 25, 2011I have the following code:
For Each wShape In ActiveSheet.Shapes
wShape.Delete
Next wShape
This works great to delete the buttons and rectangles I have on my worksheet, but it is also deleting charts. Is there any way to avoid the chart deletion?
VBA Code - Creating And Moving Shapes In Excel
Mar 9, 20141.) Create shapes (customize oval shape), I was able to create one but I think the code needs improvement to have the shape a fixed name. - see attached excel file.
2.) Move shapes into corresponding cell. Example: (Oval shape1, should be in cell g6). - see attached excel file.
Customize Heatmap creation.xlsm
Creating Shapes (Rectangles And Shapes) From Input Cells?
Aug 30, 2013Im trying to create shapes Rectangles and Squares with different colours within an excel sheet, where the length and height of the shapes is generated by input values.
Also is it possible to create 3D shapes, again where the size of the shap is generated by input cell values.
Autosize Cell Comments/Shapes Via Macro Code
Aug 25, 2006Among many attempts to insert and autosize a comment using VBA manually, which errored, i recorded the following bit. When running the macro, it does format the cell - text, not the comment - text and errors in the line highlighted in red.
Range("A14").Select
Range("A14").AddComment
Range("A14").Comment.Visible = False
Range("A14").Comment.Text Text:="Ladida"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
End With
I just want to add
.AutoSize = True
or something to that effect to the bit below. Any suggestions?
Range("A14").Comment.Text "Ladida"
Order Of Executed Code - Synching Sounds With Making Shapes Visible
Jul 8, 2014I was working on a presentation for work where I wanted to build a 'Family Feud' type board to play a game. In the board I am using activex text boxes with code that would hide the text box to reveal the answer underneath or it would show a custom shape (an X in a box) and play the buzzer sound. The code is pretty simple, first I would make the shape visible, then I would play the sound then the shape would be made invisible. If I step through the macro everything works fine, but when I run the macro, you never see the shape. It's almost as if the sound plays before the shape shows up and then it is made invisible again. I tried putting a wait and a sleep command between making the shape visible and playing the sound but that made no difference. If I remove the code to hide the shape at the end and run the macro, the sound plays and then the shape appears. Is there anyway to have the shape appear prior to or at the same time as the sound plays?
Code:
Private Sub CommandButton8_Click()
Application.ScreenUpdating = True
ActiveSheet.Shapes("First Strike").Visible = True
Play_Strike_Sound
'The previous line refers to another macro that has the code commented below. The sndPlaySound32
'function is one I picked up from cpearson.com
'sndPlaySound32 "C:\_Fin SysSoundsff-strike.wav", SND_SYNC
ActiveSheet.Shapes("First Strike").Visible = False
End Sub
UserForm Initialize Code Closes UserForm
Jul 3, 2004When I run the userform initialize procedure to reset the values in text boxes and the like instead of resetting like it should, now it closes the userform completely and then won't allow me to show it again, what could be the problem?
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
TextBox3.Value = ActiveWorkbook. Sheets("Sales Invoice"). Range("G15").Value
Dim hWndForm As Long
Dim hMenu As Long
hWndForm = FindWindow("ThunderDFrame", Me.Caption) 'XL2000
hMenu = GetSystemMenu(hWndForm, 0)
DeleteMenu hMenu, SC_CLOSE, 0&
End Sub
The Dim stuff down is to gray out the x button, there are also some module level declarations to go with that...
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long
Private Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
Private Const SC_CLOSE As Long = &HF060
the userform shows when the workbook opens and is then hidden when the user has finished entering values, then when the user goes through the process of being asked to print and save, they are then asked if they would like to create a new record, if yes then it shows the userform again, but the userform is still filled with all the stuff previously entered. I tried using the unload me instead of hiding and that wouldn't work at all, didn't give errors just didn't show the userform either, this at least shows the userform, but now when the user goes to clear the information by initializing the userform again, it simply closes the userform and then it can't be shown again either.
[url]
This is the link to the ZIP, and here are some instructions for setting it up to work.
The contents of this need to be unzipped into a folder called SyntheticShield that is placed in the C: drive that way all paths begin with C:SyntheticShield and then the other folders or files will be referenced correctly. The template I'm having problems with is the SyntheticShieldInvoiceMaker template, when you open it you'll be prompted whether you want to create a new invoice, number is final blah blah blah, say yes, then it will ask if you are importing from the Quotemaker which you aren't say no, it should then show the userform as it should. Then you can go through that process and by pressing either the create invoice or go to template directly whatever, you can hide the userform. Then you press the command button (red) a userform is brought up asking if you'd like to save without emailing, save with emailing or close controls, as for right now, I'm just getting the save without emailing to work the rest is all the same just a few tidbits of code. So click save without emailing, it should then prompt an are you sure message box, click yes it will do some things, then it will ask if you want to print, click no, then it will ask do you want to create a new invoice, click yes, this should then start the process all over again by calling the workbook_open procedure, however, when you go through everything, and click no to the import from quotemaker part it won't show the userform. And at one point it would, but then I couldn't initialize the userform without it disappearing and not being allowed to be shown...I tried putting a command button on the template to show the userform, but it wouldn't do it either.
UserForm Text Box Code (whatever Text Has Been Entered Into The Userform To Be Put Into A Cell )
Feb 1, 2010I've created a userform with a text box and a command button (Enter)
On clicking the command button I'd like whatever text has been entered into the userform to be put into a cell (Say A1).
Code For UserForm
Nov 15, 2009I am finishing a travel per diem form and I want to implement a user form where the current rates could be entered by the user and update specific cells used as my drop down list values. I have made the form and a box that activates it. I just can't write the code to place the inputted data from the form into specific cells on a sheet so the list boxes will have the current rates to choose from. I can only find information to have the data populate the next blank row of a table.
View 9 Replies View RelatedAppend Code Into Userform?
Jul 4, 2014I have written the code successfully and it is working fine for me,,But i need to Apply this code into Userform.
View 7 Replies View RelatedBuild A UserForm From Code
Dec 13, 2007Is it possible to build a simple input dialog box with only code?
I'd really like to avoid having a userform permanently in the project.
Ideally it would either display and be dismissed with code only, or be constructed as a real userform, displayed and dismissed, then deleted from the project afterwards.
I am aware of excel's built-in input dialog box, but I'd really like to get away from it's awkward keyboard functionality if it's somehow possible to do. Although that would be ideal if there were some way to make the textbox's behavior to the Home, End, Tab and arrow keys more like that of a normal textbox, but I am unaware of any such options.
Userform Textbox Code
Oct 29, 2008I have a userform and I want a textbox that counts cells J7,M7,P7,S7,V7. Does anyone know how to write this code and where to put it. All I want is the textbox to show how many cells of these five have data in them and I want it to update automatically.
View 9 Replies View RelatedCombo Box Code In Userform
Jan 18, 2010I'm a rookie with combo boxes. This is my first one.
I need a pull-down list on a form that has the integers 1 to 40. Seems simple enough, but how do I add the integers to the combo box? I don't see a property to define the list.
I have the integers on a sheet called locked and the range is Y3:Y42 if that is required.
Also, I want the default value for the combo box to be 12 if that is possible.
The form is called frmNewData and the combo box is called cboAWG
How To Write UDF Code For Userform
Mar 12, 2013Trying to write a UDF Code for a User Form. The User puts in the data for the element it is trying to find the pressure in mm Hg for.
I want a Msg Box to appear when the desired temperature is less than the lower limit temp. and the desired temp is greater than the upper limit temp.
I figured out how to get the message box to appear, but I have not figured how to get the If statement to continue with the calculation if the desired temp is within range. When I press the command button to calculate, the message box appears whether the temp is in range or not.
My code is as follows:
Private Sub CommandButton1_Click()
Dim A As Double
Dim B As Double
Dim C As Double
Dim Pvapor As Double
[Code]...
Userform: Run Code Under Another Event
Oct 19, 2006In a Userform, is there a way to run code that's listed under another UserForm event subroutine?
Example:
I have a Listbox and several Labels. When I click on a selection in the listbox, it populates the labels with various data from a spreadsheet. This code is in a "list_AfterUpdate()" subroutine.
If I change a piece of data and click a CommandButton1, I would like the labels to automatically update.
The only way I can think to do that (at present) is to run the code listed in the "list_AfterUpdate()" subroutine.
Is there a way to run that code without duplicating it in the "CommandButton1_Click" subroutine?
Add Controls To UserForm With Code
Dec 14, 2007way to allow a user to add a control to a userform without going into the VBE. Here is the situation, I am developing a userform to calculate a projected budget. I want the user to be able to select the number of controls to add, and click a command button to add them. Based on other selections that the user makes, different controls would automatically be added. Is this possible? As a follow up, will I be able to atttach code to these new controls?
View 9 Replies View RelatedVba SUMIF Equivalent Within Userform Code?
Nov 16, 2013I have a worksheet (named Deduction Worksheet) that is a running record of all part removals. A removal is denoted by a row entry that contains removal particulars. The first column (Column A) in each row contains a unique number for each part. Column K shows how many part units were removed in that removal:
Column A
... Columns ... B to J ...
Column K
Unq001
various
[Code]....
In the code above 'crng' is the criteria range, 'sValue' the criteria and 'srng' the sum range (which follows the normal SUMIF function within a worksheet). Both the 'crng" and 'sValue' values contain text, / and numbers.
note that other elements on the userform code look at data on other worksheets (not sure if this will effect this).
Possible To Have A Code To Paste Info In A Userform
Jun 7, 2009Is it possible too have a code to paste info in a userform.
View 14 Replies View RelatedConvert Code From Using Userbox To Userform
Oct 16, 2009I'm trying to convert some code to run from a userbox to run from a userform. So far everything that I've tried I'm getting an error for something.
Directly below is the code I'm running initally.
Update Code On A UserForm Via Macro
Dec 7, 2009I have a time sheet which is used by around 15 people. Part of the timesheet is a userform which adds a new sheet and names it with the seleted month and year. I have made a few changes to my timesheet which I wish to update on other peoples sheets. I am going to send out a speadsheet with a macro that people can run and it will automatically make the changes to their timesheet. I have done all the work for updating the various formats and formuals but I have hit a bit of a brick wall when trying to change the code on a user form via a macro.
It would be great if I could either overwrite all of it or add a some lines of code from a specific line number.
Adding Code To VBA Userform Progress Bar
Aug 16, 2002I have just discovered the exciting world of the additional controls that can be added to userforms. provide me with some sample code to make a progress bar increment and to activate a windows media avi file from a user form. I only need to know the code (I know how to add the controls etc)
View 9 Replies View RelatedScrolling In UserForm Frame By A Code
Feb 28, 2010Is it possible to scroll down in a UserForm Frame with a VBA Code in excel. My frame in the userform has a scroll height of 600 and a lot of option buttons. Based on a selection in a ComboBox(using If statement), I'd like to scroll down to a certain amount in the Frame.
View 7 Replies View RelatedPick Up Code Where It Left Off From Userform?
Oct 11, 2011I have a VBA function that shows a user form. In the form, the user has to select a value from a combo box. Once they do that and click the submit button, I want to return to the next line in the function. So far I've been unable to accomplish this
View 1 Replies View RelatedTab Key Ignoring Userform Properties And Some Code Being Ignored
Jul 31, 2012I have a userform with multiple text boxes and combo boxes. Firstly, the Tab Key Behavior setting for all of these boxes is set to false, and so is the Multiline setting, which should make the tab key always move focus to the next object in the Tab Order, but sometimes the tab key will ignore these settings and enter a tab in a text box instead of moving the focus to the next text box.
secondly, I have some code which should run for key presses in these text boxes to only let you enter certain characters in them. Whenever the tab key problem above occurs, this code seems to stop working and lets you enter any character you want.
Userform Initialization Code Bombing
Jan 17, 2007Private Sub UserForm_Initialize()
' MsgBox "removing outlines"
'OGA Mechanical Schemtic
Me.Label3.BorderStyle = fmBorderStyleNone '0821-1
Me.Label463.BorderStyle = fmBorderStyleNone 'Nitrogen Purge ORU
'OGA electrical Schematic
Me.Label447.BorderStyle = fmBorderStyleNone 'Hydrogen ORU
Me.Label184.BorderStyle = fmBorderStyleNone '0321-1
Me.Label468.BorderStyle = fmBorderStyleNone 'A301W1...............
VBA UserForm Code Causes Intermittent Freezing
Feb 6, 2009I have a multipage userform with about 7 pages. When page 5 is activated, it caused excel to completely lock up. This does not happen everytime but randomly, and I can't seem to figure out what is causing this.
Here is the code -
If Me.MultiPage1.Value = 5 Then
Call LaborCosts
End If
Sub LaborCosts()
If Not Range("A46").Text = "" Then
frmpricingtemplate.Label29.Visible = True
frmpricingtemplate.TextBox46.Visible = True
frmpricingtemplate.Label29.Caption = Range("A46").Text
Else
Creating UserForm Controls Via Code
Jul 22, 2005I'm making a form in excel to retrieve a number of welds. This number needs to be passed on to the next form to dynamically create an identical number of input fields (a set of text boxes and labels essentially). Any ideas on how I might do this? Is it even possible? I would like to avoid having 100 (my assumed maximum, it could get changed) sets of input fields and hiding the unused ones,
View 9 Replies View RelatedGIF Not Visible On UserForm While Code Runs
Aug 8, 2006I have some code to put a GIF animation into a web browser on a userform. This works fine - on its own. However, once the GIF has loaded I want to run a whole bunch of code in the background. If I run the code all at once, the userform appears but the picture doest show - but when I place a breakpoint between loading the userform and the main sub routine of my code - the GIF loads and the animation is shown all the time my code is running in the backgorund.
View 4 Replies View RelatedCode Stops When UserForm Shown
Nov 17, 2006I have an Access program that acts as a dashboard to open Excel reports. One of the Excel workbooks opens a form in the Auto_Open routine. My problem is that control is never passed back to access after the form opens. It appears that the code stops executing until the userform is closed. How can I open the excel workbook, show the Excel userform, and continue processing my Access code?
Set XL = Excel.Application
With XL
.Workbooks.Open FileName:=FileName, ReadOnly:=True
. ActiveWorkbook.RunAutoMacros xlAutoOpen
End With
XL.Visible = True
' Excel Code
Sub Auto_Open()
Unload UserForm1
UserForm1.Show
' The code get "stuck" right here
End Sub
Code Stops When UserForm Shows
Dec 8, 2006I am attempting to show a User Form during the forms Initialize code. I Load the form after extracting data from a SQL Server database. The code that Loads the form is returned to after the form closes. The problem is that when I run through the Initialize code where criteria decides whether the form is actually shown to the user. If the criteria is not met there are no issue, but if the criteria is met, then the form is opened and the rest of the Initialize code is not stepped through. As there are many If Then and a Select Case and a couple With...'s I need the rest of the Initialized code to be stepped through so after the form is closed by the user and the original code which Loaded the form is returned to I do not get an error.
Oh and the big problem is that I am not getting an error message during this, the Load code is actually repeated because when the original code is actually returned to is returned on the Load UserForm code.
Private Sub UserForm_Initialize()
Dim i As Integer, Endofdata As Integer, wsheet3 As Worksheet, wbBook As Workbook
Dim wsSheet As Worksheet, c As Variant, gTotal As Long, Score As String, g3000 As Long
Dim gTotalAdd As Integer
On Error Goto ErrorHandler:
Application. ScreenUpdating = False
'cmbIDCountry.ColumnCount = 2
Set wbBook = ThisWorkbook
Set wsheet3 = wbBook.Worksheets("UpdateDetails")
Set wsSheet = wbBook.Worksheets("Data") ....................