Count How Many Shape In The Activeworkbook Are Ovals
Apr 15, 2009i'm tring to count how many shape in the activeworkbook are ovals like so :
View 3 Replies
ADVERTISEMENT
Change Shape Text Without Selecting Shape
Mar 4, 2009when i run the below code i get an error 438 'object doesnt support this property or method'
View 2 Replies View RelatedUsing Activeworkbook And Thisworkbook.
Jul 3, 2009I noticed all the code that i have on a userform using activeworkbook will relate to whatever the last active workbook was "who would have guessed hay" Seeing thisworkbook used a lot i decided to go through the whole project and change all activeworkbook too thisworkbook.sheet1 During this i also made a few other changes that i was confident in, When i run it now it errors on the thisworkbook.sheet1.
Is this the wrong way of using thisworkbook to make sure the code always deals with the correct file. (will this ever be affected if the file goes through a Save As)
Or is it during my over conifedents that i have made a error somewhere else and the debuger is point me here cos it hates me.
Reference Last Activeworkbook
Jan 2, 2010I have a userform that presents the user with 4 choices and once they click on one choice a macro runs that filters and formats the data and when the user clicks on the button start over it will reopen the file they were just using prior to the changes occurring. The reason for this is the same spreadsheet is printed up to 4 times with different information (remember the part where the data was filtered?). So basically the user clicks on option 1 the macro runs formating the page and then the user clicks start over (different userform then the first one) and the spreadsheet closes and reopens before the changes.
The way I have excel reopening the file now is by having the user input the file name into a text box and clicking OK this passes the file name to the open code in VBA. How can I tell the code the name of the sheet after the sheet is no longer active (because a userform was made active)? Basically the end goal is for the user to click over and excel will just reopen the file without any user input.
ActiveWorkbook.Worksheets
Feb 16, 2009how I can make the following work no matter which worksheet I might be on?
ActiveWorkbook.Worksheets("Sheet16").sort.SortFields.Clear
ActiveWorkbook.SendMail
Apr 8, 2009I have the below code,
But i want to adapt the "ActiveWorkbook.Sendmail" so I can attach my current workbook as it is, to the email.
Private Sub CommandButton10_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "you@email.com"
.CC = "me@email.com"
.BCC = ""
.Subject = "Testing send to multiple emails."
.Body = "Please review." & vbCrLf & vbCrLf & ActiveWorkbook.FullName
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Dim Msg, Style, Title
Msg = "E-mail has been sent " & Chr(13) & Chr(10) & "Press OK to continue."
Style = vbOKOnly + vbInformation
Title = "Open Issues List"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
End Sub
Writing ActiveWorkbook Name To A Variable
Nov 17, 2009I run the macro on a currently open workbook (name could be anything) and open another workbook (name known) and step through the sheets copying various blocks of data across to the first workbook. I need to Activate the 2 workbooks alternatively as I move data back and forth and move from sheet to sheet. What I can't work out is how to determine in code what the "unknown" workbook name is so that I can make sure I activate the right workbook in the code.
I tried Activating the workbook with the "unknown" name and setting a variable = ActiveWorkbook.Name. However the value of this variable changes during the code run when you activate the other book. Is there any way to collect this value and "freeze" it so it won't change as the code runs? At the moment I have got round it by ensuring the "unknown" book is the only one open before I start and refer to it as Workbooks(1) in the code.
Allow User To Select ActiveWorkbook
Jan 21, 2009I have the following code that is going to trigger an export macro and an import macro allowing the user to upgrade the version of their report;
Sub Upgrade_SLA_Report()
x = MsgBox("Is the file you wish to export from open?" & vbCrLf & vbCrLf & "If it is click yes" & vbCrLf & "If its not click No" _
& vbCrLf & "If you need to abort the macro click cancel", vbYesNoCancel)
Select Case x
Case 6
MsgBox "Please use the Windows menu in Excel to ensure that the file" _
& "you wish to export from is the ActiveWorkbook!", vbInformation
' Missing step
If ActiveWorkbook.Name = ThisWorkbook.Name Then Exit Sub
ExpWb = ActiveWorkbook.Name
Case 7
On Error GoTo Handler
.......................End Select
Problem is, I have a missing step within Case 6 which allows the user to ensure that the file they are exporting from is the ActiveWorkbook.
1004 With ActiveWorkbook.PivotCaches.Add
Sep 25, 2009I am trying to build a macro to be placed in a blank spreadsheet for use as a template.
My development platform is Excel 2003 on Windows XP SP2. The script was initially recorded as a macro against a single data file with absolute range references and worked both on my develpment platform and on the production platform (Excel 2000 on Windows 2000). However, I have been having fits trying to convert it to dynamic addressing for the PivotCaches.Add. I develop Excel solutions a few times a year and this pivot table is the deepest I have dug into OLE code, so I am not an expert and could have a simple problem. I included the entire macro because I am unsure if there are sideeffects to what some of it is doing. I am getting a 1004 error "The PivotTable field is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field."
Since I do not get this error with the absolute references, I am puzzled as to what to fix. That is another reason for supplying the entire macro: ...
ActiveWorkbook.updatefromfile' Is Not Working
Jun 23, 2006I have a workbook that is used by many people on a network- I am editing and saving data throughout the day, and the other users are constantly having to close and re-open thier file in order to have the most recent data. today i came across the '.UpdateFromFile' command in the help file, which would be perfect, if i could only get it to work. im guessing the problem is the expression, which would be the active workbook, but' ActiveWorkbook.updatefromfile' is not working. The file is on the network, remember, im guessing this is the problem. So, im also guessing i just need to get the syntax right for the filename.
View 4 Replies View RelatedIsProtected.ActiveWorkbook.Sheets(1)
Jul 3, 2006How can I tell if a Sheet is protected (via VBA) ? (Excel 2003)
Something like: "If IsProtected. ActiveWorkbook.Sheets(1)..."
Returnig True or False will be fine.
ActiveWorkbook Code Not Running
Jun 29, 2007I have used a code to find the character "-" and replace it with nothing ("") from several named worksheets across my workbook, which OzGrid helped me with before, and it worked fine. Now I've added the code into my module (at the bottom) it doesn't work. The rest of the code works fine, and it doesn't come up with any errors when I run it, but it just seems to ignore the 'find and replace' part of it.
Sub split1()
Dim fromSheet As Object
Set fromSheet = Worksheets(1)
Dim fromRow As Integer
Dim toRow As Integer
Dim lastRow As Integer
Dim errCount As Integer
Dim thisSectionName As String
Dim i As Integer ' loop counter
Dim rng As Range 'found range
Dim emptyCellAbove As Range
Dim rng1 As Range
Dim arng1 As String 'Address of 1st hit rng1
Dim ws(7) As String
Dim ii As Integer '2ND LOOP COUNTER
Dim iii As Integer '3rd Loop Counter
Change Code To Activeworkbook
Jul 4, 2007This piece of code successfully replaces a module in a range of workbooks -
Sub CopyOneModule01()
Const strModName As String = "Module1"
Dim FName As String
Dim vWbkNames As Variant
Dim lLoop As Long
vWbkNames = Array("CFADS01", "CFADS02", "CFADS03", "CFADS04")
With Workbooks("CF266") 'copies from here
FName = .Path & "code.txt"
.VBProject.VBComponents(strModName).Export FName
End With...........................
Focus To The Activeworkbook And Not The Actual Add In Workbook
Oct 15, 2009i am creating an add in that will allow me to display all pivot tables in a workbook, their name, sheet location and source data in al listbox (called LBPivot) on a form (called FrmPivot)
My problem is that i am not sure how to make sure it points the focus to the activeworkbook and not the actual add in workbook?
Selecting ALL Worksheets In Activeworkbook As An Array
Mar 21, 2008I recorded the following macro to select all the worksheets in the Activeworkbook by clicking on the first worksheet and then hitting Shift Tab and selecting the final worksheet (thus '[Group] selecting' all worksheets in the active workbook).
Sub Macro1()
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
Range("A1:B2").Select
End Sub
How do you generalise the Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select if the number and names of worksheets varies from workbook to workbook?
ActiveWorkbook Returning NULL Error
May 17, 2009I'm developing an excel addin using Visual Studio 2008. At the moment i'm trying to display a popup showing the value contained in cell A1. However, i'm getting a runtime error saying that xlWorkBook is null. I've been able to get this to work if i open a workbook but i want to be able to execute my code on the currently active workbook. I thought that ActiveWorkbook (also tried ThisWorkbook) would let me do this but i'm having no success.
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
...
...
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlApp = new Excel.ApplicationClass();
xlWorkBook = (Excel.Workbook) xlApp.ActiveWorkbook;
xlWorkSheet = (Excel.Worksheet) xlWorkBook.Worksheets.get_Item(1);
MessageBox.Show(xlWorkSheet.get_Range("A1", "A1").Value2.ToString());
Select A Path For ActiveWorkbook.SaveAs
Oct 13, 2009I have a macro that creates and saves an XML file with a specific file name. Is there a way to modify this code in such a way that the user will be able to specify the path?
Set wb = Workbooks.Add
NewFileName = "Export For " _
& "Cust " & Customer_Data.Range("custName") _
& ", LOB " & RequestLOB _
& ", Type " & RequestType _
& ", Site " & RequestSite
On Error GoTo DoNotReplaceExistingFile
ActiveWorkbook.SaveAs Filename:=NewFileName & ".xml", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Locate A Shape Within A Shape
Jan 24, 2014On the attached spreadsheet there is two irregular shapes. "Area1" & "Area2". I need to be able to determine which area the "ball" shape is located in. If the ball is in Area1 then "Multiply 8" gets the ball and "Multiply 9" moves to the blue cell "AH39". If the ball is in "AreaB" Multiply 9 gets the ball and "Multiply 8" runs to cell "S37"
I should be able to do the moving of the shapes using all the samples, its determining which area the ball is in is the problem.
In the real spreadsheet i will probably have around 10 different Areas.
Move Two Objects - Select Case.xlsm‎
ThisWorkBook Closes Prematurely When ActiveWorkbook.Close
Jan 30, 2007I do not want ThisWorkBook to close prematurely whenever the specified Activeworkbook is closed without saving because there are other tasks to complete. The Workbook_BeforeClose dun seem to fix it for my case below.
Here’s my situation. I want to copy data from a range of a particular protected sheet in 5 (password-protected) workbooks of a similar format, to 5 worksheets(unprotected) in ThisWorkBook, which is the Master Workbook. The password of the protected sheet is common to the 5 workbooks. The passwords of the 5 workbooks are contained in a sheet in ThisWorkbook. After copying the data from Workbook1 ( 1 of 5), ThisWorkbook are prompted for closure, when Workbook1 is closed without saving. Responding to the prompt to save or not to save, forces ThisWorkbook or MasterWorkbook to break the For-Next loop. By setting Cancel = True in Workbook_BeforeClose, I can stop Thisworkbook from closure, but I will still break the For-Next loop. I have considered the method of copying data from a closed workbook, but I dun think VBA allows copying or referencing a closed workbook which is password protected at workbook and worksheet level.
Here’s my coding :
Sub UpdateMaster()
Dim MasterWB As Workbook
Dim TempWB As Workbook
Dim Source As String
Set MyWB = ThisWorkbook
For i = 1 to 5
Set TempWB = Workbooks.Open (Filename:=WB(i), Password:=password(i) )
TempWB.Sheets("ProtectedSheet").Unprotect Password:="SheetPasswd"
MyWB.Sheets(i).Range(XXX).Value = TempWB.Sheets("ProtectedSheet").Range(YYY)
TempWB.Close False ‘dun want to respond toclose ThisWorkbook else it breaks for-next loop
Next i
Application.Quit.
End Sub
ActiveWorkbook.SaveAs Only Saves One Sheet, Not Whole Workbook
Aug 2, 2006I have a csv .txt file that I am importing into Excel, and then creating a pivot-table from that data. This results in 2 tabs within my workbook, pivot-table and data. Using the following code does everything I want it to, but when I re-open the saved .xls file I only have the pivot-table sheet. I'm specifying to save the full workbook, aren't I?!?
Sub format_bgas_flowtracer()
Dim v_excel As Excel.Application
Dim v_worksheet As Worksheet
Set v_excel = CreateObject("Excel.Application")
On Error Resume Next
Kill "c:oh_for_gods_sake_JUST_WORK_YOU_STUPID_SODDING_THING.xls"
On Error Goto 0
v_excel.Workbooks.OpenText FileName:="D:gas_flowtracker_out.txt", Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _ ...............
If Formula: =IF(D4<0.38,"Thin",IF(0.38<D4<=0.48,"Good Shape","Bad Shape"))
Jun 23, 2009I have D4=0.42. I thought this formula =IF(D4<0.38,"Thin",IF(0.38<D4<=0.48,"Good shape","Bad shape")) was supposed to give me "Thin" for D4<0.38, "Good shape" for 0.38<D4<=0.48 and "Bad shape" for the contrary, but it only gives me "Bad shape" when D4 is clearly between 0.38 and 0.48.
View 4 Replies View RelatedIF On Shape
Aug 24, 2009code doesn't work
Sub test()
If ActiveSheet.Shapes.Name = ("five") Then
Range("B4").Select
Else
Exit Sub
End If
End Sub
Change Shape Name
Feb 2, 2010I have a shape in Excel called Canada. I grouped it with another shape and want this new grouping to be called Canada. But when I enter Canada in the shape name entry box, it doesn't actually change it (as it thinks I am referring to the existing Canada).
I need something like Names manager, but for shapes, not ranges.
Shape A Textbox
Apr 1, 2008Is it possible to find out if the selected item is a textbox through VB?
Get The Name Of The Shape Clicked
Jan 21, 2009Is there any way that I can get the name of the shape i just clicked to call a procedure?
I have 35 shapes in my Worksheet.
Each shape has a different name. Each shape has a macro procedure assigned, wich do almost the same procedure (with just small differences, depending on from wich shape the macro was called).
I want to make just one macro procedure and catch from wich shape the macro was called.
VBA Keep Shape Static?
Mar 21, 2014How do i Keep shape where it is no matter if i scroll up or down.
Sub KeepshapeStatic()
Dim ws As Worksheet
Dim shp As Shape
Set ws = Sheets("Sheet1")
Set shp = ws.Shapes("MyShape")
'How do i Keep shape where it is no matter if i scroll up or down
End Sub
Add Shape & Rotate
Jan 2, 2007i am trying to draw a shape (square or rectangle) using vba textbox's i have managed to draw 2 of the 4 lines.
i am stuck on the ShapeRange.Flip msoFlipHorizontal codeing.
i have attached a example of what iam trying to do.
the coorinates from the last line enterd are saved to sheet1 for use with the next line entered ...
Shape On A UserForm
Dec 29, 2007I want to create a shape connecting them and place that shape on a userform. I can create the area without any problems on a worksheet using ActiveSheet.Shapes.BuildFreeform but I don't know how to get it onto my userform.
I have looked through previous posts on similar topics and seen responses that it's as easy as copying the shape from the worksheet and pasting it into an image or frame on the userform. I can copy the shape to the clipboard without any problem. However, when I try to paste it onto an image or frame in a userform I get a message saying that the object doesn't support the use of paste. I've tried using both Selection.Copy and Selection.CopyPicture xlScreen, xlBitmap thinking it might treat a bitmap differently. When I paste I'm trying both UserForm1.Frame1.Picture.Paste and UserForm1.Image1.Picture.Paste but neither work, even though I have blank images and frames with those names on my form.
Identify Activecell Of A Shape?
May 24, 2014I have put a rectangle shape in a cell in excel. The shape is within the border of a particular cell.
Is there a way to find the reference of the cell on which that particular shape resides.
For example, I have kept a rectangle shape in cell F5. I was looking for a macro which would return the cell reference "F5" in which the shape resides.
Formula For S Shape Curve?
Dec 13, 2013I have a few dates and one Total value. I need to distribute this Total Value in such a way so I can get the same shape S curve (Date vs Value).
View 4 Replies View Related