Command button and a recorded macro

[Phprahl]

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]

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]

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]

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]

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]

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]

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]

No problem, glad I could help.

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

Andrew;?

[Phprahl]

Ok...This is probably a very stupid question, but where can I mark the thread solved?

[sandam]

np. its in the thread tools at the top of the page.