VBA - Loop Through All Images On Sheet Change Properties
Jun 18, 2013I need some code that will loop through all the images on a spreadsheet, and change the properties so the images will "Move and Size with Cells"
View 2 Replies
ADVERTISEMENT
VBA To Change Position Of Images In PowerPoint
Feb 20, 2014I have written a macro to export many bmp image files to different PPT slides. Everything works fine, but for few slides I am not able to change the co-ordinates. I am using the below code to position the image files. But no matter how much I change the values, the images sits in the same place. And I dont get any error.
PPSlide.Shapes.AddPicture Filename:=slide4pic2, LinkToFile:=msoTrue, SaveWithDocument:=msoTrue, _
Left:=475, Top:=240, Width:=245, Height:=100
PPSlide.Shapes.AddPicture Filename:=slide4pic3, LinkToFile:=msoTrue, SaveWithDocument:=msoTrue, _
Left:=475, Top:=340, Width:=245, Height:=100
PPSlide.Shapes.AddPicture Filename:=slide4pic4, LinkToFile:=msoTrue, SaveWithDocument:=msoTrue, _
Left:=475, Top:=440, Width:=245, Height:=100
How To Change The Properties Of A CheckBox Using VBA
Aug 4, 2014I am trying to use VBA to change the caption of checkboxes in "Sheet 2" when I change the value of a cell "A1" in "Sheet 1".
This code is working:
Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then Exit Sub
Worksheets("Sheet 2").CheckBox1.Caption = "New Caption"End Sub
But there are 6 checkboxes in Sheet 2 and I would like to do something like this:
Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then Exit Sub
For i = 0 to 5Worksheets("Sheet 2").Control("CheckBox" & i+1).Caption = "Box" & i+1Next iEnd Sub
This doesn't work.. So I guess the Control-function is wrong.
Change Textbox Properties
May 25, 2007I am having a hard time setting the MultiLine, Scrollbars and WordWrap properties through programmation. Actually what I want my code to do is to create a set of textboxes in my spreadsheet and then set the MultiLine, Scrollbars and WordWrap properties. Right now I can create the boxes and rename them but I'm unable to change the other properties.
inst_num = Range("AA1").Value
ActiveSheet. OLEObjects.Add("Forms.TextBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=20, Top:=95 + (inst_num - 1) * 105, Width:=70, Height _
:=30).Select
ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=100, Top:=95 + (inst_num - 1) * 105, Width:=100, Height _
:=30).Select.............
Combo Box To Change The Properties Of A Text Box
Oct 9, 2009i am trying to use a combo box to change the properties of a text box. i think i can do this by having it look at the cell link that i sent and then changing the text box appropriately with something like this:
View 3 Replies View RelatedCan't Change Combobox Properties (activex)
Apr 24, 2009How do I set the font size property and special effect property in this code?
I was trying to answer a question on Experrt Exchange but Rory beat me to it and now I'm feeling frustrated that I can't get my solution to work.
Sub AddCombo()Dim rVals As Range, rCell As Range, lTop, lLef, lHeight, lWidth, lCount As LongSet rVals = ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)lCount = 1For Each rCell In rVals If rCell.Validation.Type = 3 Then lTop = rCell.Top lLeft = rCell.Left lHeight = rCell.Rows.Height lWidth = rCell.Columns.Width With ActiveSheet.OLEObjects.Add(classtype:="Forms.ComboBox.1", Left:=lLeft, Top:=lTop, Width:=lWidth, Height:=lHeight) .Name = "NewCombo" & lCount .ListFillRange = rCell.Validation.Formula1 .LinkedCell = rCell.Address(0, 0) '.SpecialEffect = fmSpecialEffectFlat //doesn't work // '.FontSize = 14 //doesn't work // '.Font.Size = 14 // doesn't work // End With lCount = lCount + 1 End IfNext rCellEnd Sub
Change Form Button Properties With A Macro?
Nov 12, 2008I have a button wich will add data entered on a form to a spreadsheet if the data meets certain criteria. If it does not it will not let you add anything. I would like to add a second button that will add the data even if it does not meet the criteria but I need the second button to only be enabled with a password. Maybe a macro to change the button properties from locked to unlocked (true to false).
View 2 Replies View RelatedExcel 2010 :: Change ActiveX Properties
May 29, 2013It has been awhile since I've worked on anything and I can't remember how to do something that is probably very simple. I have placed an option button on my worksheet and cannot for the life of me remember how to change the properties. When I right click and select properties a box pops up but there is no info there. I want to be able to change the name and group etc.
View 3 Replies View RelatedExcel 2003 :: How To Change Properties Of File
Jun 1, 2013I have a question regarding the properties of an Excel file (.xls 2003). I can change them all (as indicated on Microsoft support website) expect the name of the last person who recorded the document (File ==> Properties==>Stat.)
How to change the name?
Macro To Change Table Properties In Outlook 2010
Jun 16, 2013The macro im using copys a table from excel into a email. Unfortunatly the email is not readable for blackberry users due to the table properties.
I need to change a few properies. This is what i do manually.....
In outlook i then hover over the table right click then select "Distribute rows evenly" then i right click again but this time i select "Table properties" i then go to the "Row" Tab
i then untick the "allow row to break across pages"
then tick "specify height" and change the value to 0.4 cm and finally change "row height is" to "exactly"
Below is the whole code im using so far....
Code:
Sub Mail_Selection_Range_Outlook_Body()
Dim rng As Range
Dim OutApp As Object
[Code]...
Option Button Properties Change Based On A Checkbox
Jun 13, 2006I would like to set up some option buttons so that the will be become visible and enabled when a check box is checked, and the opposite when the same checkbox is not check. This is my problem
Private Sub cbpDiscAlum_Click()
Dim myOption As Control
Dim myValue As Boolean
myValue = cbpDiscAlum.Value = True
If myValue = True Then
For Each myOption In pDiscounts.Controls
myOption.Visble = True
myOption.Enabled = True
Next myOption
Else
For Each myOption In pDiscounts.Controls
myOption.Visible = False
myOption.Enabled = False
Next myOption
End If
End Sub
It is the 5th command where it gets hung up: For Each myOption In pDiscounts.Controls. I am sure I am spelling both of the names correctly: the checkbox, and the group name.
Change Properties Of Controls On Ribbon Toolbar. 2007
Feb 26, 2008I've created a custom ribbon which contains checkboxes that hide or show columns.
I would like to change the enable state of the checkboxes while the user is interacting with the spreadsheet.
For example:I would like to disable all of the checkboxes if they are not viewing sheet1I would like to disable the second checkbox if the first checkbox is unchecked.
I know how to set these all on initialize. I just have not figured out how to update these controls after it has been opened.
Add Images To A Sheet
Jul 10, 2007I am putting together two reports that involves a lot of photos (we're talking about two thousand each). I have figured out how to add the images to comments thanks to other posts on this site, but is there anyway to make this more automatic?
I found this code below, which seems to apply, but I just don't know enough about VBA to make it work, my only experiance with code is Flash's actionscript.
http://blogs.officezealot.com/charle...9/27/3019.aspx
Renaming Images In Folder Using Data From Excel Sheet?
Dec 8, 2013I have an excel file which consists of old name and new name in two different columns. I have 500 photos in a folder. I want to rename the photos with the new names when there is a match with the old name(same as that of image name) in the excel.
View 4 Replies View RelatedSheet Custom Properties Show Up
Dec 9, 2006See custom document properties for a give workbook by using File, Properties but where do sheet custom properties show up.
View 9 Replies View RelatedPrint The Worksheet On Different Paper Which Requires To Go Into The Properties And Change The Paper Source From Automatically Select To Manual Feed
Jun 30, 2006When I hit the print button the worksheet prints on the paper in the bin. However, there are times when I need to print the worksheet on different paper which requires me to go into the properties and change the paper source from Automatically Select to Manual Feed. I have been trying to created a macro what will switch to Manual Feed, print the worksheet and then switch back to Automatically Select but have been unsucessfull.
View 3 Replies View RelatedLoop Through Sheet And Match Entries On Userform To Non-empty Cells In Sheet
Oct 27, 2012In the attached worksheet I have UserForm2. When I click on open compare form button on the menu sheet it opens UserForm2, I would like the information I select in the first 7 combo boxes Vegetable - Ball on UserForm2 to loop through the data in the database sheet Columns A:G and compare the entries to the non empty/not blank cells in each row. If the form contain data that matches all the non empty/not blank cells in a row in the database sheet then it is a match and should show the label and display the message. If the form entries does not match to the non-empty/not blank cells in any of the rows on the database sheet then do nothing.
The problem I am having is getting it to loop through the sheet and bring back the right results. It is only matching on row 2 of the database sheet when I select cabbage in the vegetable combo box and apples in the fruit combo box . I cannot figure out how to get it to loop through all the rows for the range I want to compare (A2:G7) - I need this range to be flexible so as data is added it will expand to read all added rows.
The code is on the btnSave_Click() for UserForm2
I attached the spreadsheet and I am explaining what I want to do and the expected result.
Fruit
Fruit Type
Vegetable
Games
Toys
Cereal
Ball
[Code] .....
What I want to do is loop through the Database sheet and if the fields on the form contain all the values in any row of the Database sheet, excluding empty cells in the Database sheet, then display a message.
So if on the form I selected Broccoli fron the vegetable combo box, Cricket from the games combo box, puzzles from the toy combo box, bananna from the fruit combo box, grits from the cereal combobox, and baseball from the ball combo box, in the databse sheet tabel shown above the match would be row 6 since the values for vegetable, game, toy, fruit, cereal and ball on the form matches what is on row 6 of the Database sheet. It does not matter what other fieds are selected /filled in on the form, the match should only take into consideration the populated cell in each row of the database sheet.
So, if the user enters Apples in the fruit combo box and Cabbage in the vegetable combo box but had blank or something other than bike in the toy combo box on form it would be a match to the Database sheet row 2, regardless of what the user enters in the remaining fields on the form
If the user enters Berries in the fruit combo box, Blueberry in the Fruit Type Combo box, Carrot in the vegetable combo box, and Grits in the cereal combo box it would be a match to Database sheet row 3, regardless of what the user enters in the remaining field on the form .
If the user enters Apples in the fruit combo box, Cabbage in the vegetable combo box, and Bike in the toy combo box on form itwould be a match to the Database sheet row 5, regardless of what the user enters in the remaining field on the form .
If the user enters Grape in the fruit combo box, Carrot in the Vegetable combo box, Cards in the game combo box, and football in the ball combo box on the form it would be a match to Database sheet row 7, regardless of what the user enters in the remaining field on the form.
If the user enters Kiwi in the fruit combo box, Cabbage in the vegetable combo box, and Bike in the toy combo box on form it would NOT be a match to the Database sheet because the Database sheet does not have a row that contain Kiwi, Cabbage, and Bike.
So basically, if the entries on the form match the exact values for all the non-empty (blank) fields for any row in the Database sheet, then it is a match.
-If the entries on the form do not contain an exact match to all the non-empty (blank) fields for any of the rows in the Database sheet, then it is not a match.
-If it is a match show the label and display the message box
-If it is not a match the do nothing
Excel 2013 :: Select Next Row / Extract Data From Next Sheet - Loop To Last Sheet
Feb 20, 2014I work for one half of a joint venture & am responsible for planning & expediting. The other half does purchasing. The bi-weekly PO download reports I receive are less than useful. I have already written the code to delete undesired sheets & add, format, and enter headers for a "Summary" sheet.
I need code to move to the next row, and run formulas to pull data from the next sheets, and repeat until there are no more sheets.
The number of sheets will vary from one download to the next, and the sheet names will vary from one download to the next.
Following are example formulas that need to be run on successive rows while pulling from successive sheets.
I am running Excel 2013 on Windows 8.1
Loop Able To Change The Last Caracter (pg=2) In VB
Dec 8, 2009I'd like to extract multiple page from a web site. I'd like to use a for loop but I'm not sure about how doing it. The problem is that the variable to increment is in the url. I want to generate a for loop able to change the last caracter (pg=2). I tried to simply replace the number by the variable but this generate error in the code.
View 4 Replies View RelatedChange To Another Variable In Loop?
Jul 30, 2014I have a list of Variables Dimentioned a Range. They are sequential; ie. A1, A2, A3, etc. How do I write the code to switch between these? Obviously, I am looking to correctly reference ("A" & aNo).
Code:
For Test = 1 To aNo
With ("A" & aNo)
.Select
[Code]......
Loop Through Each Sheet And Place Value Of Sum On Its Respective Sheet In Same Position
Nov 1, 2013How can i get this code to run through each sheet and place the value of the sum on its respective sheet in the same positon .....
Sub maths()
lr = Cells(Rows.Count, "E").End(xlUp).Row
Range("E" & lr + 1).Select
ActiveCell.Formula = Application.WorksheetFunction.Sum(Range("E2:E" & lr))
Selection.NumberFormat = "[h]:mm:ss"
End Sub
Change Text Boxes In Do While Loop
Mar 4, 2009I made up a form of 30 text boxes to simplify entering data, but seem to be having a problem inserting it easily back into my excel file.
I would like to use a simple Do While loop to shorten my code but can't seem to change my text box correctly.
My text boxes all have names like this txtap1run11, txtap1run12, txtap1run13...
As you can see all I need to do is change the last number of the text box name.
I can't figure out how to do that though. I came up with this, I don't have the do while loop in.
Change Dynamic Range While In Loop?
Sep 29, 2012I am wanting vba to loop though a range basically till the last row of the column however within the loop the row gets inserted and the lr which is pre defined does not change. I want to be able to change my lr within the loop, maybe reset it or something...or may be alternative..
I tried using a method where it will loop through a each cell in col however that takes too much times....because it is going through each column...
Code:
lr = Cells(Rows.Count,
Code:
iCol).End(xlUp).Row
For y = 1 To lr
Cells(y, iCol).Select
If ActiveCell.value "" Then
[Code]....
VBA - Change Caption Of ActiveX Checkboxes Using A Loop
Jan 24, 2013I am trying to change checkboxes caption name using a loop so that I don't have to change them individually. Here is a simplified example of what want to do:
1) I save the caption names in a array
2) I run through a loop to rename each checkboxes using the names saved in the array
Sub change_Checkbox_Caption
Dim MyArray(1to3) as string
Dim IndexMyArray as integer
MyArray(1) = "YES"
MyArray(2) = "No"
MyArray(3) = "Maybe"
[Code]...
Chart Loop To Change Axes Dynamically
Oct 11, 2006I've got an excel worksheet that contains charts 5-14, each with different max axes on primary and secondary scales. Right now the code below works to adjust the axes, however I have pasted this into a script 10 times, once for each chart with different references to the chart number, the max primary axis and max secondary axis. how I could write the following as a loop so that I don't need to have to repeat this routine in my code?
Sub ChartsBillingsShare()
ActiveSheet.ChartObjects("Chart 5").Activate
With ActiveChart
.HasAxis(xlValue, xlPrimary) = True
.HasAxis(xlValue, xlSecondary) = True
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = Range("Y6").Value
End With.................................
VBA To Subtract Cells If Negative - Row Number Change With Loop
Apr 26, 2012I have a database of over 200,000 parts
In this database i have cost and sale price. 20,000 parts have the numbers backwards.
if you loop the rows you can subtract =SUM(AE2-Y2) if that number is a negative number I would need the value of AE copied to Y2 and the value of Y2 copied to AE2. the 2 being the row number which would always change with the loop.
Event Change To Change The Sheet Name Based On A Cell Value
Jul 21, 2009Im trying to use an event change to change the sheet name based on a cell value, but my issue is how can I error trap if the sheet name is a duplicate? Here is what I have so far
Sub ChangeName()
On Error GoTo errhandler
Sheets(1).Name = Sheets(1).range("d10")
Exit Sub
errhandler:
MsgBox "sheet name is already exists"
End Sub
Conditional Formatting - Change Bottom Sheet Number Color If It Exists On Top Sheet
May 25, 2014First off I have an excel sheet that I have split into two windows. excel sheet.jpg
I am looking for a formula that will change the bottom sheet number a color if it exists on the top sheet.
Using A For Loop In Sheet
May 28, 2009how can i use a for loop in an excel sheet. For eg.
A formula should check C1 to C500 and then if the condition matches print those values in B1 to B10
Loop To The Next Sheet
Aug 11, 2009it seems REAL SIMPLE but I can't get this to work! It simply loops the code WITHIN the same worksheet, what am I doing wrong?
Sub loop()
Dim mywSheet As Excel.Worksheet
For Each mywSheet In ActiveWorkbook.Worksheets
*random code in here*
Next mywSheet
End Sub()