• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Issues with Worksheet_Calculate function

nagovind

nagovind

Member
There is an issue with Worksheet_Calculate function


please refer below codes part 1 & 2


Part 2 is working separately if the function header is as Worksheet_Calculate(ByVal Target As Range)


Part 1 is working separately if the function header is as Worksheet_Calculate()


BUT IT IS NOT POSSIBLE TO COMBINE PART 1 AND PART 2


How to combine the above 2 part as i need the operation in the same work sheet


PLEASE ADVISE


Private Sub Worksheet_Calculate()


'This is required for STAR DELTA Transformation


'SCC_Tx-Utility Module


'-------------------------------------------------------------------

'Part 1

'-------------------------------------------------------------------


If Worksheets("SCC").Range("D48").Value = 0 Then

Worksheets("SCC").Shapes("Picture 410").Visible = True

Else

Worksheets("SCC").Shapes("Picture 410").Visible = False

End If


'-------------------------------------------------------------------

'Part 2

'-------------------------------------------------------------------


If Target.Address = Worksheets("SCC_Tx-Utility").Range("AD29") Then

Worksheets("SCC").Range("AD29").Select

Selection.Copy

Worksheets("SCC").Range("AD41").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Worksheets("SCC").Range("AD31").Select

End If


End Sub
 
Hi Govind ,


Try this :


Private Sub Worksheet_Change(ByVal Target As Range)

If Application.Intersect(Target, Range("AD29")) Is Nothing Then Exit Sub

Application.EnableEvents = False


'This is required for STAR DELTA Transformation


'SCC_Tx-Utility Module


'-------------------------------------------------------------------

'Part 1

'-------------------------------------------------------------------

If ThisWorkBook.Worksheets("SCC").Range("D48").Value = 0 Then

ThisWorkBook.Worksheets("SCC").Shapes("Picture 410").Visible = True

Else

ThisWorkBook.Worksheets("SCC").Shapes("Picture 410").Visible = False

End If


'-------------------------------------------------------------------

'Part 2

'-------------------------------------------------------------------


ThisWorkbook.Worksheets("SCC").Activate

ActiveSheet.Range("AD29").Select

Selection.Copy

ThisWorkbook.Worksheets("SCC").Activate

ActiveSheet.Range("AD41").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

ThisWorkbook.Worksheets("SCC").Activate

ActiveSheet.Range("AD31").Select

Application.EnableEvents = True

End Sub


This entire procedure should be in the SCC_Tx-Utility worksheet code section.


Narayan
 
@ NARAYANK991 thanks


its not working


first there is error message if i use ThisWorkBook. in part 1...if i remove that there is no error message but part 2 is not working now


please help
 
Hi Govind ,


I cannot understand what is happening , since everything works correctly on my computer !


I'll tell you what I did :


1. Created a new , blank worksheet.

2. Renamed Sheet1 to SCC.

3. Renamed Sheet2 to SCC_Tx-Utility.

4. Opened Visual Basic , and copied the code to the SCC_tx-Utility section.

5. Inserted a picture in sheet SCC , and renamed it "Picture 410".

6. Put 1 in cell D48 in sheet SCC.

7. Changed the contents of cell AD29 in sheet SCC_Tx-Utility.

8. The picture in sheet SCC was no longer visible.

9. Changed cell D48 in sheet SCC to 0.

10. Changed the contents of cell AD29 in sheet SCC_Tx-Utility.

11. The picture in sheet SCC was now visible.


Do you see anything wrong in this test method ? Can you verify all this in the uploaded worksheet ? The link is :


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21126


Narayan
 
@ NARAYANK991 thanks


I'm extremely sorry if i mistaken


Your excel file is working well....thanks


My query is different


Actually i need to have both function in the same worksheet


Part -1 and Part -2 function should work in the same sheet that is my requirement


One is ...cell AD29 to be copied to AD41....when there is any input / change in cell AD29...this is independant of any other function


Other is picture change / Picture switching will depend on the change in cell value D48..so both different function shall be in the same sheet...this is independant of any other function


Any solution please....!

thanks
 
Hi, nagovind!

First of all, the "Worksheet_Calculate" isn't a function but an event of the worksheet object. As that event is has this code structure:

Private Sub Worksheet_Calculate()

' here all the stuff

End Sub

As you can see there are no parameters passed as arguments, so you can't have the code under the form of: "Worksheet_Calculate(ByVal Target As Range)".

If you try to do so and you compile the code, you'll get an error quite like this:

Compilation error. Procedure declaration doesn't match with event description or sub/procedure with the same name.

Neither as Sub like I stated nor as Function as you stated in your first post. So something's wrong in the original description of the problem, I think.

Regards!
 
Hi Govind ,


Your first version of the VBA code was checking for the Target in sheet SCC_Tx-Utility , which is why I suggested that you put the complete code in the sheet SCC_Tx-Utility.


But now you say that you want both functions in the same sheet ; I assume this sheet is SCC.


If so , remove the code from the sheet SCC_Tx-Utility , and paste it in the section for sheet SCC. It should still work.


Narayan
 
@ NARAYANK991 thanks


Still the same issue exists


because of the below code part 1 working only there is a change in cell AD29


If Application.Intersect(Target, Range("AD29")) Is Nothing Then Exit Sub

Application.EnableEvents = False


But i need to have the code in which part 1 and part 2 is independent ..events in the same sheet.......


please advise
 
@ NARAYANK991 THANK YOU


Please find attached the file with the actual requirement in the below link


Please advise


https://skydrive.live.com/redir.aspx?cid=5e11a6e8320428fe&resid=5E11A6E8320428FE!105&parid=root
 
Hi Govind ,


Just change the following statement :


If Application.Intersect(Target, Range("C1")) Is Nothing Then Exit Sub


to the following :


Set two_cells = Application.Union(Range("A1"), Range("C1"))

If Application.Intersect(Target, two_cells) Is Nothing Then Exit Sub


Narayan
 
Narayan,


Continued


Its working fine...but improving the code while doing the calculation i have ended up with the change of cell through the dropdown menu so i have used the drop down combo box for input menu from the linked cell i need to switch on/off the picture


Now the issue is WORKSHEET change event is not detecting the cell changes by the combo box link cell....how to trigger the worksheet change event for the change of value in the combo box linked cell using the same code


Please advise
 
Hi Govind ,


There are two ways this can be done. Check out the links :


1. https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21129


2. https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21130


Narayan
 
Hi Narayan


Yes its working in your file....but i have used the COMBOBOX under Form control for similarity i need to use the same.....


But in the example sheet it is the combobox under ActiveX control....


Is it possible to code this using the previous case (Combobox - Form control)


In VBA combobox under form control is shown as Dropdown20....DropdownXX...XX number


Any tips please


thanks in advance
 
Hi Govind ,


I don't think it should make any difference ; you can copy + paste the code into your worksheet , and make the necessary changes ( instead of combobox1 and combobox2 , put in the names of your comboboxes , instead of Combobox_Change , there should be a corresponding event for the dropdown box ) ; everything should work correctly.


In case it doesn't , let me know.


Narayan
 
Hi Narayan,


Sorry i'm unable to integrate the code to my original file so i'm attaching the original file by retaining the required fields and vba code


Please refer to the below link


https://skydrive.live.com/redir.aspx?cid=5e11a6e8320428fe&resid=5E11A6E8320428FE!106&parid=5E11A6E8320428FE!104
 
Hi Govind ,


Can you check this out ?


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21132


Narayan
 
Hi Narayan,


While operatinmg the dropdown 54 which is linked to the cell FY24...picture 410 (in GOLD RECTANGLE) has to switch on and off...which is not working
 
Hi Govind ,


The macro Show_Pic10() is invoked when you click on Picture 53 ; the macro Show_Pic20() is invoked when you click on Picture 52.


These two macros control the visibility / invisibility of :


1. Picture 52

2. Picture 53

3. Drop Down 54


The visibility / invisibility of Picture 410 is controlled by the value in D48 , which in turn has the following formula :


=IF(OR($FL$24=1,OR($FY$24=1,$FY$24=2)),0,1)


I have no idea what this formula does ; can you specify under what conditions , you want Picture 410 to be displayed , or under what conditions it should not be displayed ?


Narayan
 
Hi Narayan,


thanks you are right


In my previous mail...i have written the wrong cell address asF Y24...sorry


Picture 410 has to switch ON/OFF based on the Cell/ cell formula in D48


D48 cell formula is based on many other conditions in the original file...


The aim is to switch on/off the picture 410 based on the cell D48 formula which depends on the many other parameters


thank you
 
Hi Narayan,


Is it possible by using any temporary sheet for detecting the cell change using the dropdown cell...any solution please
 
Hi Govind ,


Sorry if I am not able to understand your requirement ; can you please be more specific and detailed ?


Narayan
 
Hi Narayan,


Here are the details


File is located here


https://skydrive.live.com/redir.aspx?cid=5e11a6e8320428fe&resid=5E11A6E8320428FE!107&parid=root


There are 2 sets of same operation related to Pic A&B and Pic C&D in the excel sheet


Pic A&B forms one group and Pic C&D forms another group. Operations of group 1 and 2 are identical with different locations (independant of each other) of switching the / masking the picture


Sequence of Operation


1.Click Pic B -excel sheet

2.Pic A will appear

3.Drop down menu will appear (with the selection of "Cu+Al") right to Pic A&B

4. other fields such as cellAW24, BE26, BK26, BQ26 are visible now

5. point no. 4 is true only when the drop down menu is selected as "Cu+Al" OR "Al+" if drop down is selected as "Open" or "Solid" then the cells BE26, BK26, BQ26 are not visible only the cell AW24 is visible

6. Invisibility of the cells AW24, BE26, BK26, BQ26 is controlled by PICTURE in white colors.

7. The above is the sequence of operation for group 2 also...with some additional masking/ visibility of other drop downs


Now the query is HOW to add an additional feature to this worksheet by retaining all the above functions’ as it is


Additional feature is nothing but COPYING cell AD29 to AD41 after entering the value in AD29 irrespective of other functions


Note: Switching of picture over the cell should be triggered by DROPDOWN CELL SELECTION…LINKED TO CELL /DEPEND ON CELL RESULT D48


thanks......please help
 
You must log in or register to reply here.
Back
Top