Toggle Shape Visibility On Different Sheet Than Event Code
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Embed Picture Into Worksheet & Toggle Visibility
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 Replies!
View Related
Toggle Shape Color
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 Replies!
View Related
Event Triggered When A Shape Is Moved
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 Replies!
View Related
Sheet Event Code: Find Last Row Broken?
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 Replies!
View Related
Sheet & Workbook Event Code Not Firing
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 Replies!
View Related
Sheet Event Code Errors If Target Deleted
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 Replies!
View Related
2007 Vba To Toggle Sheet
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 Replies!
View Related
Toggle Sheet Visiblity Via CheckBox
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 Replies!
View Related
Ensuring Visibility Of Text
I created an automation process where worksheets are generated. But after the sheets are generated I noticed that one of the company names is missing from the list. As I am watching the worksheets generate I can see the name quickly printing on the screen. When I use a message box to validate the data entry, the name does appear. After all is generated, the name is still missing. Does anyone know what this could be? Could it be a software malfunction?
View Replies!
View Related
Event Code Firing More Than Once
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 Replies!
View Related
Visibility Of Cell A1 On All Worksheets When Macro Finished
How can I loop through all worksheets in a workbook and, not only select cell A1 in each worksheet at the end of a macro, but have the visibility of cell A1, no matter where you last clicked on the worksheets, such as a remote cell like BK244. I would like the user to see the first row and further most left cell (cell A1) when they click any sheet in the workbook, because my macro causes several rows of data to be written, leaving the viewer to see something like row 244, instead of the top row which is where the titles are.
View Replies!
View Related
Hide Toolbars & Restore Visibility
I have a procedure that stores the Commandbar names and visibility settings in an array on open. Another procedure hides all Commandbars on open and restores their original visibility settings on close. For some reason the "Standard" and " Formatting" toolbars don't return to visible on close. My error log says "08/13/07 18:21:37 [QA.xls]MCommandBars.bRemoveMenus(), Error -2147467259: Method 'Visible' of object 'CommandBar' failed". I have tried manually testing the hide/unhide procedure with hard coded settings and checked the correct variables are being sent and all seems to be correct. I am pretty confident that the procedure to store settings is correct but have included the code just in case, also just noticed the "Standard" and "Formatting" toolbars are the only two visible at programme start. Public gsaMenus() As String Public Function bMenuSettings() As Boolean Const sSOURCE As String = "bMenuSettings()" Dim bReturn As Boolean Dim iCounter As Integer Dim cbrMenu As office.CommandBar
View Replies!
View Related
Create Event Procedure Via Code
I'm trying to add a worksheet event via code. It works OK when I run it on its own but I get a run-time error 9 subscript out of range error when I try to run it from another procedure. Please find attached my 2 bits of code. Any help greatly appreciated. This is the bit that works OK on its own but not when called from my other procedure Sub Code_To_Write_Code() 'This writes code to the new sheet Dim StartLine As Long Dim SheetToAddCodeTo SheetToAddCodeTo = ActiveSheet.CodeName With ActiveWorkbook.VBProject.VBComponents(SheetToAddCodeTo).CodeModule StartLine = .CreateEventProc("Calculate", "Worksheet") + 1 ..............
View Replies!
View Related
Validation Code For Change Event
change the below code from a worksheet_change to a worksheet_calculate method. and still do the same action. the reason I am changing methods is due to the fact that the validation will not trigger the worksheet_change event to fire. this is my attempt to find an alternative way to fire off the macro.....
View Replies!
View Related
VBA Code Triggered By An Event
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 Replies!
View Related
Event Triggered Code Not Working ?
I have a manually calc'd workbook with the following code Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("VAL1CELL")) Is Nothing Then Me.Calculate If Not Intersect(Target, Me.Range("VAL2CELL")) Is Nothing Then Me.Calculate If Not Intersect(Target, Me.Range("CHOICE")) Is Nothing Then Me.Calculate If Not Intersect(Target, Me.Range("$L$36")) Is Nothing Then Me.Calculate If ActiveCell.Address = "VAL1CELL" Then Range("VAL2CELL") = Range("Y$41") End Sub Everything works as it should other then the part that is If ActiveCell.Address = "VAL1CELL" Then Range("VAL2CELL") = Range("Y$41") When the user selects VAL1CELL This is cell B2 and is a drop down, I want VAL2CELL which is C2 and also a drop down to show what is in Y41 (i.e the first name that appears in the drop down...not a thing happens ? is there a flaw to my code ?
View Replies!
View Related
Clear Event Handler Code
I wonder if there is a way to clear event handler code in a userform programmatically? Haven't quite managed it yet. Something like: dim x as integer With ThisForm.CodeModule x = .CountOfLines For 1 to x .line = "" Next x End With
View Replies!
View Related
Allow User To Disable Event Code
I have these codes in place in my excel file, there is nothing wrong with them at all. Here is the the problem : My superior runs the macros in place in the file almost daily, and when he is done he then closes it and all is well. The macros does things to other files so they are changed in a way that the supervisors like them. I avoided putting macros in these individual files, because of the attempt to make it as simplistic as possible without them being able to affect overall file. The problem happens when he (my superior) accidently makes a change in the primary sheet in the workbook with all the macros, it affects alot of my formulas and macros that are already in place. Within this workbook, it keeps a log of things from the other files results, so I have to have a record of it in the workbook, hence having a save event in place. The Question: Is it possible to create a macro button, that will allow him to disable the codes below, so that he can close it and open again, so that it doesn't show any of the errors that happens by accident and try to close it again so there is no errors? Basically, I am asking what is the code for this, and where would I place it in. Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim mylogoff Set mylogoff = Sheet1. Range("A65536").End(xlUp).Offset(0, 1) mylogoff.FormulaR1C1 = "=NOW()" mylogoff.Copy mylogoff.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = Fals mylogoff.Offset(0, 1).FormulaR1C1 = "=RC[-1]-RC[-2]" Application.DisplayAlerts = False Sheets("Start Here").Select ThisWorkbook.Save ThisWorkbook.Close End Sub......................
View Replies!
View Related
Limit Event Code To Range
I've been using these things called ranges, but I'm not even really sure what they are or how to use them effectively. I want to be able to make the following macro only applicable to the range, of anything below F5,G5 and I5. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("$F:$I")) Is Nothing Then Target.Font.Name = "Marlett" If Target = vbNullString Then Target = "a" ElseIf Target = "a" Then Target = "r" Else Target = vbNullString End If End If End Sub
View Replies!
View Related
VBA For Worksheet Event Code
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 Replies!
View Related
Copy Event Code To Other Workbooks
Am trying to copy some code from the ThisWorkbook object into about 100 other Workbooks. I know how to Import and Export Modules, but when I export code from ThisWorkbook in saves it as a CLS file. Upon export a new class module is created. I simply want to create code that will automatically copy the code from one VBA project ThisWorkbook object to another VBA project ThisWorkbook. I have the code to open all the files etc just need to figure out how to import the code from ThisWorkbook.
View Replies!
View Related
Multiple Targets In Event Code
I found this great macro here but I need it target more than column 1. does anyone knoe how to target column 1 and 3 at the same time? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then If IsEmpty(Target) Then Target.Offset(0, 1).Value = Empty Else Target.Offset(0, 1).Value = Now() End If End If End Sub
View Replies!
View Related
Change Event Code Not Firing
I ran this code last week and it worked great, but today it doesn't work at all. I have even deleted it, closed Excel and and started fresh. Is there some small thing I'm missing (like hopping on my left foot while entering a code) ...
View Replies!
View Related
Add New Worksheet With Event Code
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 Replies!
View Related
Userform: Run Code Under Another Event
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 Replies!
View Related
Prevent Event Code Running
I know how to enable/disable events using VBA code, however is there an option within excel to turn it on/off? My problem is this... At the beginning of my code I disable events and at the end I enable it again (I need to do this to avoid being caught in a loop). However something is going wrong somewhere in my code and the code stops halfway through. I'm trying to test sections of the code, but I often inadvertently stop the code without enabling the events again. Therefore I can't get my VBA to execute again unless I close excel down and restart. This is a pain as I have to find my place in the code again!
View Replies!
View Related
Add Event Code To New Worksheet
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 Replies!
View Related
Set Control Visibility Based On Tag Property
I have a userform which uses the tag property of the frame to determine whether a frame is visible (and hence the controls that reside within the frame). A frame contains three combo boxes, and six text boxes. The tag property of the frame matches the number of frames that are visible on the form, so that if the user selects five frames, frames 1 through five become visible and for all other frames visible = false. A frame contains all the data for a single entry. There can be up to fifty frames/ entries that are visible on the form depending on the user selection. I want to use the visibility property to do two things: first the combo boxes are filled from an array after the user selects the number of frames (or entries). I only want to fill the comboboxes where the frame is visible. The second thing is that I have a function which uses the data from the text boxes and combo boxes as required arguments. Since these are required arguments, I will get a data mismatch error if I try to call it and the controls are empty. Therefore, I only want to call the function if the frame which houses the controls is visible. Public Sub Visible1(Entry As String) Dim ctrl As Control For Each ctrl In UserForm2.Controls If TypeName(ctrl) = "Frame" And ctrl.Tag <= Entry Then With ctrl .Visible = True End With Else If TypeName(ctrl) = "Frame" And ctrl.Tag > Entry Then With ctrl .Visible = False.............
View Replies!
View Related
Changing A Workbook Event Handler Via Code
I have a workbook, that when opened the first time needs to prompt the user to save it. I got that working with no issues by using Private Sub Workbook_Open() SaveOnOpen End Sub where SaveOnOpen is a procedure in another module. What I would like to do now is re-assign the Workbook_Open sub to be set to null, so that it doesn't run any more. Is it possible to somehow assign Workbook_Open to call a null procedure, as opposed to setting up an onTime call to delete the code itself?
View Replies!
View Related
Add Code To Worksheet Event Using VBA
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 Replies!
View Related
TextBox Triggers A Code On The Exit Event
I have a Userform with a textbox1 and a commandbutton. TextBox1 triggers a code on the exit event. CommandButton1 simply unloads the UserForm. If a user enters data in the textbox, but does not exit and instead clicks the commandbutton to unload the UserForm, the exit event code is still triggered and then for some reason the commandbutton no longer responds to being clicked. I can't seem to find a way around this. If the commandbutton is clicked, I simply want the UserForm to be unloaded and the exit event for the textbox ignored
View Replies!
View Related
VBA Code To Add Event To New Worksheet
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 Replies!
View Related
Qualifying Code For Event Driven Macros
I am encountering an error(-2147417848) when running my existing code. I did a little research and found on MSKB Q319832 information that suggests that I might try qualifying my code (not that I know what that means : D) In the article, it suggests using something like the below: Dim oXL As Excel.Application Set oXL = New Excel.Application oXL.Visible = True ..... ..... oXL.Quit Set oXL = Nothing I tried this approach and it fails as soon as it hits the event portion of the macro: If oXL.Target.Address = "$C$2" Then .... End If I need to understand how the oXL object is used against information in my existing workbook.
View Replies!
View Related
Remove Event Code & Copy Sheets
I have a macro that copies 3 sheets to a new workbook but I do not want to copy worksheet properties. Attached is my Macro that I use but when the user accessed via the webpage It will have sheet properties(macro) attached. my Desire is to not have macros on my new work book. Sub WEBPAGE() Dim wbkNew As Workbook Dim wbkCurrent As Workbook Dim xlCalc As XlCalculation xlCalc = Application.Calculation Application.Calculation = xlCalculationManual On Error Goto CalcBack Application. ScreenUpdating = False Set wbkCurrent = ThisWorkbook....................
View Replies!
View Related
Undo Option Lost After Event Macro Code
I have a sales spreadsheet, that people on this forum have very kindly helped me with by giving me two macros; one to remind users that they need to update the month cell when an order comes in, and the other to automatically put the date in a cell when any cell in that row is changed. The final thread is here: Message To Remind That Cell Is Mandatory and the Macro used is: Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range Application.EnableEvents = False For Each c In Target If c.Column = 11 Then If c.Value = "100 - Purchase Order In" Then MsgBox "Is the Month In correct?" End If End If If c.Column > 1 And c.Column < 18 Then Cells(c.Row, 1) = Now End If Next c Application.EnableEvents = True End Sub However... We've now found that we cannot undo anything in these spreadsheets. If, for example, a cell is incorrectly copied or deleted, the only way of undoing the change is to shut down the spreadsheet without saving! Is this just a by-product of using the time macro (a search on other threads suggests that it might be), and, if so, is there any way of changing it?
View Replies!
View Related
Determine If Worksheet CommandButton Fired Event Code
When I click on a commandbutton20 on sheet1 it moves the user from sheet1 to sheet2, hides some columns & formats the height of a row in a range of cells I will refer to as Area X. On sheet 2 I have a target condition that if the user clicks on any part of Area X it move the cursor to cell C8. What I want to accomplish is that if commandbutton20 in clicked, the target condition is suspended. otherwise the target condition works. The code I wrote is as follows. What happens when I press F5 to execute it... a macro window pops up. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("a1:x3,e4:x6")) Is Nothing Then If Worksheets(1).CommandButton20_Click() = True Then Exit Sub Else: Range("c8").Select End If End If End Sub
View Replies!
View Related
Change Event Code To Run Macro When A Cell Value Changes
I have looked at a series of Change Event topics and code but can't see what I need. I simply want a macro to run automatically when a cell ....which contains the Maximum time from a range.... changes. I assume I use .... Private Sub Worksheet_Change(ByVal Target As Excel.Range) ...but I have no idea what code to use...
View Replies!
View Related
Textbox Click Stops Event Code Running
I have a textbox from the drawing toolbar. When someone changes a cell then clicks in the textbox, Worksheet_Change does not run. If they double click in the cell, that's OK I can capture that event and protect the sheet, stopping them clicking in it. But if they just start typing in the cell, I can't capture that. I have seen some API code which captures keypresses, but it is not practical to use as it loops repeatedly. I could lock the textbox and have the user do something to unlock it, but this is a last resort.
View Replies!
View Related
|
TRACKER
