How to Embed Flash in Excel (Office Applications)

---

Query Source : Excel Macros Google Group
Solution Type : Flash and MS Excel
Query by        : Mahesh Parab
Solution by     : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

---

Query / Problem:

Dear All, Any one know the way how to embed a flash project into Excel ?

---

Solution:

1. Select the sheet in which you want to embed the flash project.

2. Go to View -> Toolbars -> Control Toolbox and click on the icon (More Controls in the last) which looks like a little hammer.

3. Select “Shockwave Flash Object” from the alphabetical list.

4. Now your cursor has been reformed into the plus shape, this allows you to create a rectangle shape on the sheet to make space for your Flash Object.

5. Right click on the cross-shaped rectangle box object and click on properties.

6. In the properties box, double click the first option. i.e.Custom

7. Now do the following options as suggested in the “Property Pages” dialog box.
    i.   Provide the Movie URL
    ii.  Check the “Embed Movie” checkbox.
    iii. Check the “Play” checkbox.

8. Click Apply and then Ok.

9. Close the properties box and Control Toolbox.

---

Change Picture or Image Location

---

Query Source  : Excel Macros Google Group
Solution Type  : VBA Macro
Query by              : Carlos Pedro (Sister Ray)
Solution by         : Ashish Jain (MCAS; MCA; Lead Trainer, Success Electrons)

---

Query / Problem: I’m trying to change a Picture object that is included in a Excel Worsheet, since I haven’t had much sucess I was wondering if it is possible to change the image source (the file that is being displayed) using VBA.

---

View Solution Here

---

Color only a part of cell using Macro or REPT Function

---

Query Source    : Excel Macros Google Group
Solution Type   : VBA Macro
Query by               : ExcelUser777
Solution by         : Ashish Jain (MCAS; MCA; Lead Trainer, Success Electrons)

---

Query / Problem:
HI
Basically i’d like to only show coloring in part of a cell, so for example
Cell A1 is grey only 33% of the cell is grey -I’d like to color one cell grey 33%. I have several cells to color, different percentages. Once I see one macro I can create the other percentages. Is that possible with a macro?
Appreciate all your help.
Excel 2003 preferably
Thanks,
ExcelUser777

---

Solution:

1. Using Macro

i)   Select the range (It should be in terms of percentage, else change the following code accordingly.)
ii)  Press Alt+F11.
iii) Paste the following code in Code window.
iv) Run the macro and enjoy.

View VBA Macro Code here.

---

2. Using REPT Function

i) Use REPT function to repeat the pipe symbol, | as displayed in formula box Below.

---

How to use VLookUp Function?

---

Query Source    : Excel Macros Google Group
Solution Type   : Functions and Formulas
Query by               : Gargi, FICO-SAP
Solution by         : Ashish Jain (MCAS; MCA; Lead Trainer, Success Electrons)

---

Query / Problem:
Dear All,
Can any of you please help me with how to work on V Look Up , and use functions such as transpose and concatenation. I am working In SAP as FICO .It would be of great help if any of you can give other  relevant functions required for SAP datas to be taken up in Excel.
Thanks in Advance
Regards,
Gargi

---

Solution:

The VLOOKUP function returns a value of a cell from one of the table columns. The VLOOKUP function is an extension of the LOOKUP function.
The letter V at the beginning of the function is an abbreviation for vertical. VLOOKUP searches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify in the table.

Figure 1: Shows the results and use of Vlookup function.

i) Searching a value

ii) Searching and Returning a value

Figure 2: Shows the Arguments Box for Vlookup function.

Description of Arguments:

1.Lookup_value: The value for which the calculation is made or the value to be searched in first column of Table_array.
2. Table_array: The range of data where lookup_value is to be searched and the range containing the result.
3. Col_Index_num: Column number of result column. This can be from 1 to total number of columns in Table_array.
4. Range_lookup: This can be True(1) or False(0) only as you need exact match or approximate match respectively.

---

Access Search Engines on Browser (Internet Explorer) using VBA/Excel

---

Query Source   : Excel Macros Google Group
Solution Type   : Internet Explorer and VBA
Query by          : Dave K
Solution by       : Ashish Jain (MCAS;MCA;Lead Trainer, Success Electrons)

---

Query / Problem:
Is there code that could perform the following:

1. Copy the data that lies in the active cell.
2. Go to an open window (in explorer or mozilla) of http://www.ebay.com (or open an entirely new window of ebay.com, if that makes it easier).
3. Paste the data into the search box at http://www.ebay.com (which by default is the active field when a new window of ebay is opened)
4. Hit “enter” at ebay to display the search results.

I am new to VBA and computer programming, but any suggestions or guidance you can provide would be very much appreciated. 

Solution: 
1. Press Alt+F11. (To write the macro in Code Editor.)
2. Go to Tools –> References –> Select “Microsoft Internet Controls”.

3. Copy-Paste the code available here.

4. Now, run this macro as desired and whenever required.

---

CEILING and FLOOR Function

---

Query Source   : Excel Macros Google Group
Solution Type   : Functions and Formulas
Query by          : DM
Solution by       : Ashish Jain (MCAS;MCA;Lead Trainer, Success Electrons)

---

Query / Problem:
Ok, can someone help me out?  I am trying to create formula that is going to round my numbers to x.47 or x.97.
A1=$9.99
B1=75%
C1=A1-B1=$2.49
So what I am trying to do is to take discount from price and if it ends x.49 I would like to round it x.47 and if it ends x.99 I would like to round it to x.97. Does this make sense….so everything between x.51 and x.99 should be rounded to x.97 and everything between x.01 to x.49 should be rounded to x.47. 

---

Solution:

Assuming the contents as follows
Column A (Actual Price) – A1 = 9.99
Column B (Discount)      – B1 = 75%
Column C (Sale Price)    – C1 = ‘=A1*(1-B1)‘

The Solution would be
Column D (Tag Price)     – D1 = ‘=FLOOR(C2,1)+IF(CEILING(C2,1)-(C2)<0.5,0.97,0.47)‘

---

Example:

---

Resize Form using Worksheet Event macro

---

Query Source  : Excel Macros Google Group
Solution Type   : Forms and VBA
Query by          : Kanwaljit
Solution by       : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

---

Query / Problem:
I have a User Form which needs to take a specified size depending upon the data source. Data Source is in Range DataS1 and DataS2. The following range (named as “Dimensions”) shows the dimensions which the User Form needs to have when ever we use DataS1 or DataS2.UserFormSpec    DataS1      DataS2
FormWidth                   50            100
FormHeight                  40             80

Cell A1 of Sheet1 contains a dropdown containing the names of the datasources, i.e., DataS1 and DataS2. The following code is used to show the UserForm.

Private Sub MyForm_Click()
        UserForm.Show
End Sub

What is wish is that when I select DataS1 or DataS2 from Cell A1, the UserForm width and height changes according to the numbers in range “Dimensions”.

---

Solution:

Brief Description: Solution requires use of Event Macros, VBA, Forms, Object Hierarchy. Even if you don’t know refer below to solve such problems in a very easy manner.
As you want to re-size the form whenever value in Cell A1 of Sheet1 changes, you mustuse event macro to achieve this. Since event macros are automatically fired in response to associated events. Following are some examples of excel events for which event macros are already in the MS Excel applications:
      Name of Event                           |             Event Macro
1. Opening of Workbook              |          Workbook_Open()
2. Double click a cell                     |          Worksheet_BeforeDoubleClick
3. Activate a Sheet                        |          Worksheet_Activate()
4. Pivot Table Update                  |           Worksheet_PivotTableUpdate
5. Change Cell Value                     |            Worksheet_SelectionChange
etc etc etc

Since, each event macro is associated with an event, it is also associated with a particular object. Like, if defined then a workbook event macro is associated to the workbook where it is defined. So if you’ve written a workbook event macro in Workbook A then it won’t affect Workbook B. Same for Sheets, pivot tables etc.

Full Solution to the Query:
1. Open the desired Excel file.
2. Go to VBE by pressing Alt+F11.
3. Under Microsoft Excel Objects for this workbook, Double Click on Sheet1 to open it’s code window.
4. Paste the Code Available here in it.

5. Test your Macro by changing the value of Cell A1.

---

 Example: 

---

ViewColor Entirerow based on a column’s value

---

Query Source   : Excel Macros Google Group
Solution Type   : Event Macro and Data Validation
Query by          : Avinash Daga
Solution by       : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

---

Query / Problem:

I have two buttons on column P which called active and deactivate.When I click active it should change color of  that row as green,means that project is active and once that is completed i can deactivate that by changing it to original text color. This is working for raw 1 but when I am dragging it down to make it work for all rows I am not able to do so.Please help me to have that thing changed for each row. 
Link to Problem File

---

 Solution:

Instead of using buttons a better solution is to include Data Validation (as you’ve used it in other columns) and Worksheet Event Macro (Worksheet_Change). Here is how you can do this:
Data Validation
1. Select Cell P2.
2. Go to Data –> Validation –> Settings.
3. Change Allow to List and Source to Active,Deactive. Click Ok.
4. Copy the cell P2 using Fill series/Fill Down or any other method you wish.

Use Worksheet Event Macro
Event macros should be placed only in the code window of target object. Since in this case our target is Sheet 3, we will place our macro in Sheet 3 only. This is because event macros only listens the events with which they are attached to. Here is event macro for this problem.

---

View VBA Macro Code Here:

---

Example: How and where the macro is to be placed?
Link to Solution File.

---

Show the First Day of Current Week

---

Query Source   :Excel Macros Google Group
Solution Type  : Custom Formula
Query by          : Savio
Solution by       : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

---

Query / Problem:
is there any way to have a cell in excel update itself every week to show the first day of the week?
say the week starts on a monday, the cell displays 23/02/09, retains this value thoughout the week and displays 02/03/09 the following monday.

Solution:
1. Select the desired cell.
2. Press F2 and paste the following formula in it.
     =WEEKNUM(NOW())*7*(YEAR(NOW())-2008)+39804
3. press Enter and you’re done with solution.
Example:

---

Count 5 Consecutive Zeroes

---

Query Source   :Excel Macros Google Group
Solution Type   : User Defined Function
Query by        : Lavprasad Kori (MIS Executive, Hindustan UniLever Limited)
Solution by       : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

---

Query/Problem :

I want an urgent help for the below metioned data.Attaching hererwith the data file.

I want a help to count the zero value if it comes continues 5 times or more than 5 times then count as 1 time & If it comes continue less than 5 times then ignore it.

Solution:

1. Press Alt + F11.

2. Paste the following code there.(Create a User Defined Function)

---

View User Defined Function Code Here

---

3. Press Alt+Q.

4. Type “=Count_5_Zeroes()” in AD4 as above in the picture.

5. You’re done in just 4 simple steps.

---