Toggle Shape Visibility On Different Sheet Than Event Code
May 29, 2008
I have found that this only works on the sheet being viewed, how can I make it work on a different sheet than the one that I am on?
Option Explicit
Private Sub Worksheet_Calculate()
If Range("C1").Value = "5" Then
ActiveSheet.Shapes("rectangle 1").Visible = True
Else
ActiveSheet.Shapes("rectangle 1").Visible = False
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
View 8 Replies
ADVERTISEMENT
Dec 18, 2007
I am trying to embed a picture into a worksheet, hide the picture, then call it up later when a macro runs. I know how to insert a picure using a macro, but I want to embed the picture so that I can take the file with me that includes the picture and be able to call the picuture in my macro. Right now, the picture is on my hard drive and the picture will not load if I take the file to a different computer and run the macro.
View 4 Replies
View Related
May 31, 2007
I am trying to write a macro so that when you click a circle shape it will fill it black and then when you click again it will go back to no fill. Like the option button but is not linked to any other one. So it should just work like a check box but I need it to be a circle.
View 4 Replies
View Related
Jan 20, 2010
Is there a way to trigger a macro when a shape is moved by the user? If I set the OnAction property for a shape, then the cursor changes to a hand when it is over the shape, and I can detect a mouse click on the shape, but I cannot drag and drop the shape. If I reset the OnAction property to "" (empty quotes), then I can drag and drop again within the worksheet, but I can't trigger the macro.
Worksheet_Change or Worksheet_SelectionChange are not triggered by this event.
People have suggested using timers to continuously poll the shapes and determine their locations, but is there an easier way?
What I am trying to do is create a sheet where the user can visually move around objects (in this case representing employees) and deposit them in various zones. The spreadsheet would then apply certain attributes to the shape i.e. change colour according to where the shape is and if it is in an unsuitable zone. I can do all of this, but I want it to work the instant the shape is moved, not rely on the user to hit a button.
View 9 Replies
View Related
Jul 24, 2009
I'm making a macro to show or hide rows depending on whether or not a checkbox is checked. I'm using Wingdings font checkboxes, not actual form controls. The question: My macro can't find the last row of the used range on the sheet. This is in the sheet's code, set to fire when the selection changes.
Normally the line in blue works just fine, but it's not working here. In the attached workbook, the macro thinks the last used row is row 19, when it's actually row 21. What's going on?
View 2 Replies
View Related
Jun 27, 2008
why this code does not work when the worksheet is changed between range "B1:F5"?
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:F5")) Is Nothing Then
With Range("B1:F5")
Cells(Target.Row, 7) = Cells(Target.Row, 6).Value + Cells(Target.Row, 5).Value
End With
End If
End Sub
View 3 Replies
View Related
Jan 28, 2014
Error message: Unable to set the Visible property of the Worksheet class
View 1 Replies
View Related
Mar 25, 2008
I have added this bit of code to change the apperance of entered time from 0835 to 08:35
UserInput = Target.Value
If UserInput > 1 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
And it works like a charm. Except that if the content in one of the cells later is deleted a "Run time error 13" is the result. Debug leeds to the line "If Userinput >1 Then"
Can this error be avoided..?
View 3 Replies
View Related
Jan 27, 2009
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
View 2 Replies
View Related
Mar 27, 2009
Is there a way to write a Worksheet_SelectionChange (ByVal Target As Range) event in module after creating a sheet in VBA? I constantly delete a sheet, then repopulate it with a new one that is empty, but I need to add some code that happens if they should change a particular cell. It worked when I ran it on a worksheet without refreshing, but as soon as I cleared and repopulated the sheet, it was gone. Is there a way to preserve this?
View 9 Replies
View Related
Aug 3, 2013
This code toggles between a blank cell and an "a" each time the user selects V81.
Code:
If Target.Address = "$V$81" Then
If Target.Value = "" Then
ActiveCell.Value = "a"
Else
ActiveCell.Value = ""
End If
End If
I'd like to expand it so this toggle works not only on V81, but also on V89, V100:105, and V120:124.
View 8 Replies
View Related
Jul 28, 2009
i need some code to be able to toggle design mode, i am creating embedded flash games in excel and i want to be able to change the game using a drop down and this code:
View 3 Replies
View Related
May 29, 2009
I m sure this should be simple but the excell help in 2007 is infuriating
I have a macro invoked by ctrl t to toggle the worksheet that I am useing. I want it to work out which sheet is open and open the other one if the macro is invoked
here is the code I have got but it goes to the first sheet and then wont come back
Sub Atmos()
If Sheets("Work").Select Then
Sheets("Atmos").Select
End If
GoTo Last
If Sheets("Atmos").Select Then
Sheets("Work").Select
End If
GoTo Last
Last:
End Sub
View 9 Replies
View Related
Apr 3, 2008
I'm building a worksheet and I been struggling with writing a macro for this particular checkbox. As an example, I have Sheet 1 which contains my checkbox. When the checkbox is checked I want it to unhide sheet2 and when it is not checked, I want it to hide sheet2 again.
View 3 Replies
View Related
Apr 14, 2013
I have a shape called "Company Logo". Code to copy the shape from sheet 1 and paste to sheet 2?
(I tried macro record but had trouble when I pasted it into another macro).
View 4 Replies
View Related
Mar 13, 2014
I have made several Shapes and assigned macros to them, all is good.
What i am curious about: is it possible to highlight or change colour of the shape on press and return to normal if another is shape is pressed? Just anything to make that particular shape stand out that it has been selected then return to normal when another shape has been selected...
[Code] ....
That is the code so far that is attached to 1x shape...
View 5 Replies
View Related
Jun 25, 2012
With VBA I need to add a rectangle to a sheet and have the shape named. They usually start out as "Rectangle 1", but I would like to set the name with the code.
View 9 Replies
View Related
Mar 2, 2012
I have a peice of code that i know is inefficient and it is in danger of becoming too large. I have a spreadsheet that has circles aligned to each cell. There are around 100 in total. The code changes the colour of the shape based on the cell value in which it sits. However, the code needs changing and also it does not automatically update the colour shape even though the cell value changes. I have to manually select a cell and then the formula bar and then press return for it to update.
I am using excel 2010.
This is the code i am using for each shape.
Code:
If Range("n12").Value = text Then
ActiveSheet.Shapes("Oval 250").Fill.ForeColor.RGB = RGB(255, 255, 255)
End If
[Code]....
View 6 Replies
View Related
Mar 4, 2009
when i run the below code i get an error 438 'object doesnt support this property or method'
View 2 Replies
View Related
Sep 2, 2006
Is is possible to use VBA to remove/delete a macro and also remove code like this on worksheets:
Private Sub Worksheet_Change
End Sub
Private Sub Worksheet_Activate()
End Sub
View 4 Replies
View Related
Oct 3, 2007
Is there any way you can use VBA to assign a key to toggle between two different cell formats? I want to toggle a cell from white border with black text to black border with white text and visa versa, with the same key or button. I've searched the posts but can't find this mentioned.
View 6 Replies
View Related
Feb 27, 2008
I need to to use VBA to copy a worksheet (which i've managed to do!), but I need the new worksheet to have a Worksheet_Change event. Now when I copy the worksheet, the event doesn't copy over (obviously as its a cut and paste jobby). Any ideas on what code I need to add in the Worksheet_Change event just after the new worksheet is automatically created?
View 14 Replies
View Related
Jan 17, 2007
I have seen many examples posted here that are close to what I need, but I am not experienced at writing code, so I am not sure how to make the changes to this code that apply to what I need. So I will try to explain what I am looking for & hopefully not be too long winded:
I have an excel workbook that has several worksheets within it - 10 of the worksheets are identical as far as the formulas that are in each of the cells, however, they are all VLOOKUP cells that refer to another excel workbook (used as a "database")which lists all of our projects - there is a cell in each of the 10 sheets that can be changed that will allow that particular worksheet to access the information in the "database worksheet" for the particular project name that is entered in this cell.
There is currently an event worksheet code for each of these worksheets which allows for a picture to be displayed "floating" above cell (L13) based on the project name that is showing in this cell- however it is based on all of the project pictures "living" in each worksheet (the picture that is called up by the project name is displayed while the rest are hidden - as per the formula)- the code is shown below:
Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("L13")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub
However - this option works fine when there are 5 or 10 pictures/projects - but we are looking to grow our project database. So, I was hoping to be able to store the pictures in another location (such as another worksheet or in a file on the server - I would also appreciate input if anyone has an opinion on which would work better?) and have some type of worksheet event code that can be written in to each worksheet that would access the picture in this "central" location and have them appear in cell (L13) of each worksheet based on the project name displayed.
View 9 Replies
View Related
Jan 25, 2008
I have 2 worksheets ('pathways', 'pathway events').
'pathways' has unique rows with a unique ID i.e. [Pathway ID] whilst 'pathway events' has the same initial column [Pathway ID] but with multiple values of the same [Pathway ID] value.
If an [Pathway ID] value is selected in 'pathways', I want it to trigger some code which will open a new worksheet and copy the multiple rows in 'pathway events' with the same [Pathway ID] value and paste them into the new worksheet.
Is this possible to do in Excel. I normally use Access and you can have triggered events.
View 9 Replies
View Related
Jan 26, 2010
i need to add a DoubleClick event to about 40 workbooks.
each has 6 sheets and the code will be added to two of them.
i can cycle thru the folders/subfolders and open the correct files.
but how do i put the code into the specific sheets using a program?
View 9 Replies
View Related
Oct 19, 2006
In 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?
View 6 Replies
View Related
Nov 2, 2006
i try with what limited knowledge i have, if you dont mind take a look at the code below, i read your article and added the appropriate line, the code works fine except the msgbox has to be ok'd twice before it exits sub any ideas why?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim MyCell
Set rng = Range("A2:A100")
If Not Intersect(Target, rng) Is Nothing Then
With rng
For Each MyCell In rng
If MyCell = "" Then
MyCell.Select
MsgBox "Please use this next blank cell"
Exit Sub
End If
Next
End With
End If
End Sub
View 2 Replies
View Related
Aug 20, 2007
Is it possible to copy a "Worksheet_change event" macro to a new worksheet by macro? Like when I insert a new worksheet, a certain macro, for example "run macro on data entry", to be already written in its worksheet_change event.
View 7 Replies
View Related
Oct 3, 2007
I'm having trouble copying a macro to a newly created sheet. I do like this:
ThisWorkbook.VBProject.VBComponents("Kopier1").Export filNavn
For Each kopSheet In ThisWorkbook.Worksheets
If Left(kopSheet. Name, 8) = "Inddata-" Then
kopSheet.Copy
ActiveWorkbook. SaveAs ThisWorkbook.Path & "" & kopSheet.Name
ThisWorkbook.VBProject.VBComponents("Kopier1").Export filNavn
Workbooks(kopSheet.Name & ".xls").VBProject.VBComponents. Import filNavn
ActiveWorkbook.Close
End If
Next kopSheet
I copy 6 sheets, named "Inddata-*", and i wan't to copy a module named "Kopier1" with it. I know that i can use an add-in, but that is unfortunately not a good idea in this project. It does export the module "Kopier1", but, it doesn't import it to the newly created workbook!
View 2 Replies
View Related
May 28, 2008
time-to-time an unexpected eventcode appears in my worksheet modules
View 10 Replies
View Related
