How do you add Code? 1

[harrisharris]

I have seen so many answers stating the solution is to "add the following code" etc. In the case of a spreadsheet, can someone explain exactly how one "adds code". An example is Paul Brickers solution:-

You can try this. In the Format Event for the Detail section put
If DateDiff("d",[DownLoadDateField],Date())>2 Then
[DownLoadDateField].FontWeight = 800
Else
[DownLoadDateField].FontWeight = 400
End If

I would really appreciate a pointer on this.

Many thanks

 

[KenWright]

With any spreadsheet open, hit ALT+F11 together and you should see the Visual Basic Editor.

At the top left you should hopefully see a window with Project - VBAProject as it's title. Inside here it will be a similar kind of structure to an explorer style interface when you are browsing folders, but it should contain somewhere in the list the name of the Workbook you are in. If you are in Book 1 then that you should see VBA Project (Book 1).

Right Click on the VBA Project (Book 1) bit and then left click where it says 'Insert', then left click on 'Module' in the menu when it appears. You should now see a folder below the title VBA Project (Book 1) in the window, and if you expand the folder that says Modules you will see a branch that says Book 1. Double click on that branch and you should see a big blank white area open up on your screen to the right of the Window you are looking at.

This is the code window where you will usually put examples of code in. Paste in the following text:-

Sub MakeUppercase()
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula = False Then
cell.Value = UCase(cell.Value)
End If
Next cell
End Sub

Sub MakeLowercase()
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula = False Then
cell.Value = LCase(cell.Value)
End If
Next cell
End Sub

Sub ToggleCase2()
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula = False And cell.Value = UCase(cell.Value) Then
cell.Value = LCase(cell.Value)
Else
cell.Value = UCase(cell.Value)
End If
Next cell
End Sub

Now do File / Close and return to MS Excel.

Now in your workbook if you go to Tools / Macro / Macros, you will see 3 macros that are available to you entitled:-

Sub MakeUppercase()
Sub MakeLowercase()
Sub ToggleCase2()

If you type some text into your worksheet now, and the run each of those macros, you will see the text turn from lower to upper with the first, upper to lower with the second, and you can just keep running the last if youw ant and it will toggle the case to the opposite each time.

Regards
Ken................

 

[harrisharris]

That's brilliant Ken - I now have lots of experimentation to do - this has opened up a new world for me in Excel. No doubt I will be coming up with more questions in the future.

Regards

Laurence

 

[KenWright]

If you wanted to see examples of code you could point your newsreader in the direction of the Microsoft Excel Newsgroups at msnews.microsoft.com, and look at microsoft.public.excel.programming in particular. You will also find some other really good Excel groups.

Other places to find examples of code can be found at the following link:-

http://j-walk.com/ss/excel/links/xllinks2.htm

In fact John's site alone is well worth a trawl.

Regards
Ken...............

 

[Cresby]

Display the Toolbox Toolbar. Click on the triangle/set square icon. Click on a button icon and draw that button somewhere. Double click on the button and the Code window comes-up automatically. You now have not only code but a means of making the code run - a clicky button.