1.) 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
Im 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.
How do i draw shapes on the fly on forms?
View 4 Replies View RelatedI 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?
Among 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"
I 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
I have an Excel (.xlsm) that opens an existing PPT and uses VBA to modify several slides.
There are several shapes on each slide and my .xlsm has VBA that modifies those shapes.
(The slides have designs on them for different size business cards which get filled in with spreadsheet names,...)
Now I want to "select all" the shapes on a slide as a "group"(not sure that term is correct). Then I want to export that group as a .PNG image file. Save location can be the same folder location of the .xlsm file (doesn't really matter).
This is different than exporting each slide as a .PNG.(which I can already do). In this case the business card designs on each slide are different sizes. So I need the images to be just the size of the "group" on that slide.
The VBA code is within the .xlsm file (not the PPT)
Several posts show VBA code for within the .pptm file that works fine if using just a PPT
However that code doesn't seem to work when I put it in the .xlsm file to drive the image creation from Excel.
PC with latest of all software.
I would like to use Shapes in excel, instead using userform as the code below.In this userform, it has command buttons for the numbers and the a text box to show the numbers.How can I do it, using square shapes for numbers and another rectangle shape, to show the written numbers.I would like to create a number keyboard using "shapes".
[Code]....
Is it possible to create a piece of VBA that displays a comment box when a user rolls over a specific shape in Excel?
View 1 Replies View RelatedI am struggling to get a macro code for zooming in and out the shapes. I have a macro which fulfill half of my requirement. By assigning the below macro to the shape I am able to increase the current size to double by clicking on the shape however I need the same macro should retain the original size upon second click.
Here is my code to increase the size.
[Code] .....
"The requirement: Once after assigning the macro, on first click it should double the shape size and on second click it should retain the original size."
I did use the solution in a thread by Andy to make a picture or shape rotate. It works perfectly as a standalone. With this I mean all the information on the same sheet as per example in the file 901259.
I'am using it to rotate a dial. The problem is instead of using the value from the sheet itself I link the scrollbar to a cell on a different sheet in the same workbook. There some calculations are done and the value which makes the dial rotate is linked to that sheet. The dial however is on another sheet.
The calculations are almost displayed realtime but the dial is lagging. How would I go about to "solve" this lagging.
I'm using Excel to create a flowchart from a configuration sheet. I'm not manually entering the coordinates. Instead, I draw the start step in a set location. The next step(s) that's connected to the first step is placed directly below the start step. if there's more than one step connected to the start step, it's placed at the same y-coordinate but shifted along the x-axis.
The problem I'm running into here is this: say I have three steps connected to the start step and three further steps connected to the first of the three connected to the start step, how can I keep track of this and shift the other two steps connected to the start step along the x-axis so they're not positioned above the four steps below the first step (and as such, in the same location as other steps)? I'm not sure if my description is particularly clear or not so I've attached a drawing that will hopefully clear things up.
Is there any way of giving Excel shapes a "weight" so that other shapes won't line up on top of them?
Using Excel 2007.
I have shapes in 3 different rnages of cells on a cell worksheet:
R1 = $D$5:$D & lngRows (row=32)
R2 = $G$5:$G & lngRows (row = 51)
R3 = $M$5:$M & lngRows (row = 50)
I need to set the properties of the shapes in each range differently The shapes in R1 are Left + 46 But the shapes in the other 2 ranges need to be just left My problem is in this bit
Code:
'Set properties for each shape in ShapeRange
For Each sh In ws.Shapes
I need to be able to set properties for each range separately instead of the entire sheet. Full code below (only Range $D at the moment - works)
Code:
Option Explicit
Sub AddShape3()
'Purpose: Add small rectangles to database table/fields for
' brainstorming and documenting relationships and queries
'
'Resources:
[Code]....
Ii have a UserForm with a ComboBox in it. the selections in the box are
0
5
10
15
20
25
30
I have 7 shapes in Worksheets("Interface") and renamed each shape caution1 through caution7
What i need is when i click on continue in my userform each shape will change according to the selection.
if 5 is selected all caution shapes will read, Caution flag is out 5 minute break
if 10 is selected all caution shapes will read, Caution flag is out 10 minute break
if 15 is selected all caution shapes will read, Caution flag is out 15 minute break
and so on
excel 2007 btw
how to change the default settings for shapes in Excel 2007? I select a line tool and it is a blue color and I would like to have its default as black
View 6 Replies View RelatedI have a workbook with 5 Tabs. One of these tabs is "Completed" (for completed work) The other tabs are names of Managers and the tabs contain information about who is doing what work for the Manager and information about it.
What i would like to do is in column F on every sheet is the "status". I would like when the "status" is changed to completed, to have VB code move that entire record to the Completed tab.
I think its possible i just don't know how to do it.
Workbooks.Open "E:Prep2008" & "" & Format(ActiveSheet.Range("C6"), "ddd dd-mm-yy") & ".xls"
is code I use to open a workbook (workbook b) based on the date found at cell c6 of workbook A.
Question ... how do I divert activity to to the newly opened WB B from WB A?
Workbooks?.Select?
I’m looking to create an invoice-type format from data in a row but only when the client requests it.
So, if there is a Y in column R. i.e. the client wants an invoice format then.
I have been given a “template” format by the team manager that I have to use. Its far from ideal, well for me anyway, for moving things around and I’m struggling to get anywhere with it.
I’m trying to take the data from the row in the source / client worksheet, which will be created via a code from here, and transpose it to a copy of the “template” worksheet which sits within the same workbook. This is made more complicated because in some cases the data for the invoice will be in two or more rows, because there will be a few items on the same invoice, but they will still be on the same worksheet.
The source / client worksheet has data from columns A to T with a header in row 1.
The details will need to be transposed from the relevant row from each column to the “template” worksheet as follows
From column in source / client worksheet To cell in copy template worksheet.
From - To
CB2
AB3
BB5
HB7
IB9
GB11
EB13
FB15
RB19
SB21
QB23
OB25
PB27
This is where it gets messy, or more messy should I say
MB33
JB35
LB37
KB39
TB41
NB45
For each of the six sections M to N above there may be more than one relevant items which will be on rows 3 onwards of the source /client worksheet.
So, ideally I’m guessing based on if there is data in column A of the source / client worksheet then M to N above will need to copied downwards, i.e. below itself on the template worksheet say from column A and B rows 33-45 copied to A and B rows 47-59 until all the data is copied over.
Blinking eck ... this is a nightmare… a real nightmare. This more than one section above is really bad… I’m at a complete loss.
Although if its really not possible they may have to have another template worksheet with the second, third etc items on as I just can’t see this working.
I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.
View 5 Replies View RelatedI have a workbook containing 2 sheets called ('EPM Extract' and 'Sheet B'). Column 'B' of the 'EPM Extract' sheet contains a list of Project IDs (numerical), and 'Sheet B' also contains a list of Project IDs (also in column B), plus all of the additional data I require.
Now, from the required cell within the 'EPM Extract' sheet, the normal Excel the code would read as =vlookup(B3,'Sheet B'!B5:W32,11,False), upon which I would drag down the code from that cell to all other required cells below. However, these are my issues:
* How do I even begin to write the code within VB, as I know that variables and arrays have to be set
* I need to apply the vlookup across 12 columns as I am bringing back 12 different results (i.e. I need to copy results from column counts 11 to 22 from 'Sheet B' to columns in 'EPM Extract' sheet.
*The amount of data in the sheets will vary each month, so I need to know how to apply the code only to the amount of rows in the 'EPM Extract' sheet, so some kind of R1C1xl.UP will need to be defined also.
I have a workbook with four worksheets named
FilteredData
FilteredDataFormat
array
arrayFormat
The two sheets named FilteredDataFormat and arrayFormat exist strictly to preserve headers , and column widths.
So that for convenience sake I can clear my data without specifying a range that excludes the headers.
I simple clear the contents of the 'FilteredData' and 'array' then copy and paste 'arrayFormat' and 'FilteredDataFormat'.
Sort of an unconventional and lazy approach but I see no reason why this will not work.
Here is my original code and what happens is that excel creates another workbook for some reason unknown to me
For some reason the following code creates a blank page everytime. Is there a way to have it not do this that I am not seeing?
View 2 Replies View Relatedhow to create commandbutton code so let me explain what I am trying to do and anyone that could provide me with some basic code details to solve my problem:
When the command button is clicked in an excel worksheet I want a pop up box to come up containing specific information sourced from two other excel worksheets depending on the commandbutton. What I want to know is what code do I enter from the visual basic view in order for the box to appear with the right information.
I need to distribute it to several areas of my company. Each area only needs part of the report. Therefore I have written some code which splits the existing report by area and creates a brand new report with that smaller chunk of data in it.
However I now realize that the new sub-report will require some code of it's own in order to function. My problem is that only the sub-report will be distributed to the end user. The core report will not. Therefore any code written for the new sub-report must be included within the sub-report.
I have a long list of zip codes (this is just a small portion of the list) that I need to make into ranges. Is there a formula I can use?
From this list:
90080
90081
90082
90083
90084
[Code] ......
To this:
To From
9008090084
9008690089
9009190091
9009390099
9010190103
9017490174
9018590185
9018990189
9020190202
9020990213
9022090220
I have a macro I am using that uses several date filters. There are filters for the previous month, previous 6 months, previous year, all dates in the future, etc.
Every month I have to edit the macro and change the dates in the macro so it remains accurate. Is there any way I can modify the macro so that the dates are stored in a cell within the workbook and not in the macro?
What I would like is to type the date ranges in a sheet within the workbook and have the macro look to those cells for the date filter instead of typing in exact date ranges within the macro. This way the actual macro code does not need to be modified every month. I would like to only have to change cells within the worksheet and not touch the macro code.
My macro does some calculations for a worksheet. I need the macro to loop through all the worksheets regardless of how many worksheets there are. My first sheet is a summary page with the names of the subsequent sheets that the macro needs to do calculation on. I need the macro to recognize the worksheet names in the summary page and run for each worksheet name. For instance the next time I run the macro I may have fewer sheets of more sheet names in the column. It sounds almost like an Indirect function problem.
View 9 Replies View RelatedI'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 RelatedIs there a formula that can be used to convert a manufacturing LOT Number to a Date? For example, the first six digits of our Lot Number are the MMDDYY followed by an item description (120913DKP-01). We are trying to pull the 120913 portion out of the lot number to equal a Date of Manufacture of December 9, 2013.
View 4 Replies View Relatedi have one excel sheet (refer attach) i give some example 7 to 8 customers, but in sheet nos. Of customer name a to z suppose to 3000 customer list. Now i want to create automatic customer alfa- numaric code.
View 2 Replies View Related