PDA

View Full Version : [SOLVED] Command button and a recorded macro



Phprahl
03-31-2005, 01:53 AM
Hello,



I?m still working on my little project and I have a question about a command button and a recorded macro.



I would like to use ONE button for two macros (that probably could be combined into one). The thing I want to do with the button is to change numbers 1-15 in different cells to letters a-o. That works fine with the recorded macro below. When the macro has been executed I would like the text in the button change from a-o to 1-15 and when pressing the button again, it will change back to 1-15.



Can the macro below be refined and altered as I described it?



Rgds,

/Phprahl

Sub Change()
' Change Makro
' Makro indspillet 31-03-2005 af pp
Range("D12:D14").Select
ActiveCell.FormulaR1C1 = "a"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Fed"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("D18:D20").Select
ActiveCell.FormulaR1C1 = "b"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Fed"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("D24:D26").Select
ActiveCell.FormulaR1C1 = "c"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Fed"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("D30:D32").Select
ActiveCell.FormulaR1C1 = "d"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Fed"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("D36:D38").Select
ActiveCell.FormulaR1C1 = "e"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Fed"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("D42:D44").Select
ActiveCell.FormulaR1C1 = "f"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Fed"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("D48:D50").Select
ActiveCell.FormulaR1C1 = "g"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Fed"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("D54:D56").Select
ActiveCell.FormulaR1C1 = "h"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Fed"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("D60:D62").Select
ActiveCell.FormulaR1C1 = "i"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Fed"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("D66:D68").Select
ActiveCell.FormulaR1C1 = "j"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Fed"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("D72:D74").Select
ActiveCell.FormulaR1C1 = "k"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Fed"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("D78:D80").Select
ActiveCell.FormulaR1C1 = "l"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Fed"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("D84:D86").Select
ActiveCell.FormulaR1C1 = "m"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Fed"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("D90:D92").Select
ActiveCell.FormulaR1C1 = "n"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Fed"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("D96:D98").Select
ActiveCell.FormulaR1C1 = "o"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Fed"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("F12:F14").Select
End Sub

sandam
03-31-2005, 02:48 AM
This should do what you're after.

HTH
Andrew;?



Sub Change()
Dim I as integer
For I = 2 to 16
Range("D" & (I*6) & ":D" & ((I*6) + 2)).Select
Select Case ActiveCell.FormulaR1C1
Case "a" to "o"
ActiveCell.FormulaR1C1 = I - 1
Case 1 to 15
ActiveCell.FormulaR1C1 = chr(I+96)
End Select
Next I
Range("F12:F14").Select
End Sub


Oh, this code will do the formatting but i'm not to hot on spreadsheet/document based controls - I use Userforms myself. But if you place this sub in your personal.xls then you shoudl be able to use it by going to tools -> macros -> Change()

Killian
03-31-2005, 03:38 AM
Hi there,
I was working on a similar approach to Andrew but using collection of defined ranges and a UserForm - The code below goes in a UserForm with a button called "CommandButton1"
It updates the mode/button based on the first range value.


Option Explicit
'make boolean available to all procudures in the from code
Dim ToLetter As Boolean

Private Sub UserForm_Initialize()
'call procudure to check if were coverting alpha to numeric of vice-versa
CheckMode
End Sub

Private Sub CommandButton1_Click()
Dim RangeCollection As Collection
Dim c As Range
Dim i As Integer
'make a collection of target ranges
Set RangeCollection = New Collection
RangeCollection.Add Range("D12:D14")
RangeCollection.Add Range("D18:D20")
RangeCollection.Add Range("D24:D26")
RangeCollection.Add Range("D30:D32")
RangeCollection.Add Range("D36:D38")
RangeCollection.Add Range("D42:D44")
RangeCollection.Add Range("D48:D50")
RangeCollection.Add Range("D54:D56")
RangeCollection.Add Range("D60:D62")
RangeCollection.Add Range("D66:D68")
RangeCollection.Add Range("D72:D74")
RangeCollection.Add Range("D78:D80")
RangeCollection.Add Range("D84:D86")
RangeCollection.Add Range("D90:D92")
RangeCollection.Add Range("D96:D98")
For i = 1 To 15 'there are 15 ranges
For Each c In RangeCollection(i)
c.Value = i
'ascii character "a" is 97 so if we're converting to letters
If ToLetter = True Then c.Value = Chr(96 + i)
'do some formatting on each cell as required
c.Font.ColorIndex = 5
Next
Next
'update mode
CheckMode
End Sub

Private Sub CheckMode()
If Range("D12").Value = 1 Then
ToLetter = True
CommandButton1.Caption = "1-15 to a-o"
Else
ToLetter = False
CommandButton1.Caption = "a-o to 1-15"
End If
End Sub

Phprahl
03-31-2005, 04:44 AM
Thanks both Andrew and Killian for your quick replies...

I tried your code, Andrew and it works fine apart from probably a range problem I will look at. Then I tried your code, Killian but I didnt make it work. I dont think I have found out the benefits of using a UserForm yet. Why is it better to have a userform and how do I access it when working in the sheet without going in the VBA editor?

Rgds,
/Phprahl

Andrew,

Is this range correct?...When I change from numbers to letters it starts with "b" and when I change back to numbers the 14 and 15 is still letters...


Range("D" & (I * 6) & ":D" & ((I * 6) + 2)).Select

Rgds
/Phprahl

Killian
03-31-2005, 04:44 AM
It's better to have a user form if you want to interact with the user. They are best when collecting information and selecting options. If you just want to run a marco, a toolbar button is better.
The command userformname.Show will display the form. This can be used in any macro, a Button on the worksheet, etc
I've attached an example that shows the form when the workbook opens

sandam
03-31-2005, 05:22 AM
the one line of code was off

it should be


ActiveCell.FormulaR1C1 = Chr(I + 95)
'not

ActiveCell.FormulaR1C1 = Chr(I + 96)


the loop start at 2 and not one which is why its 95 and not 96 :)

Phprahl
04-04-2005, 01:51 AM
Thanks alot for your help, I have managed to get it to work with the worksheet button just the way I wanted. Would not make it without your assistance...

Rgds

/Phprahl

sandam
04-04-2005, 01:54 AM
No problem, glad I could help.

Don't forget to mark you're thread solved with the thread tools.

Andrew;?

Phprahl
04-04-2005, 02:01 AM
Ok...This is probably a very stupid question, but where can I mark the thread solved?
:banghead:

sandam
04-04-2005, 02:14 AM
np. its in the thread tools at the top of the page.