You are here:

Excel/One click auto fill

Advertisement


Question
Hey bro,

Just trying to figure out how to attach a hyperlink  kind of button to a cell so when I go to click on it, it updates all cells in a range with “no".

An example:

- A1:A500 contain a list of materials
- B1:B500 contain prices of each materials
- C1:C500 contain the word no

My objective is to scroll down the list and change “no" to “yes" in certain cells in column C so I can create a formula to only look at the yes's, add up the costs of the materials I'll be using for a specific job and provide a quote. After this, I would like to be able to click on a hyperlink kind of button Something in a cell and have ALL the “yes's" revert back to “no".

Thanks bro 👍

Answer
That's a macro, not a hyperlink.

This macro will do what you've outlined:

Sub ResetNOcells()
   Activesheet.Range("C1:C500").Value = "no"
End Sub


How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

You can also drop a shape or a Forms button onto the sheet and link that macro to the button.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.