Command button

[Ron99]

Hi,

I have created command buttons in my worksheet, I want to add codes which is in modules to that button, unfortunately it is not adding. Is there a way to do this ? other than copying the code from mdoules and pasting it in the command button.

Thanks
Ron...

 

[VoG]

If you add buttons from the Forms toolbar you can assign them to code in your modules.

 

[Ron99]

Hi,

I have added 2 modules to a button, it works...but I have a problem with one!, when I try to add that module, I get a message which says " reference must be to a macro sheet "

I have pasted the code below


Public Sub Ron99()
Dim LR As Long, _
LC As Long, _
dWs As Worksheet, _
sWs As Worksheet, _
rowx As Long, _
Rng As Range, _
rng1 As String


Set sWs = Sheets("GSC Activity").Select
Sheets.Add After:=sWs
Set dWs = ActiveSheet
dWs.Name = "Pending Task"
rowx = 2
Application.ScreenUpdating = False
LR = sWs.Range("A" & Rows.Count).End(xlUp).Row
LC = sWs.Cells(1, Columns.Count).End(xlToLeft).Column
dWs.Cells(1, 1).Value = "Name"
dWs.Cells(1, 2).Value = "Activity"
dWs.Cells(1, 3).Value = "Due Date"
With sWs.Range(sWs.Cells(5, 6), sWs.Cells(LR, LC))
Set Rng = .Find("Pending", LookIn:=xlValues)
If Not Rng Is Nothing Then
rng1 = Rng.Address
Do
dWs.Cells(rowx, 1).Value = sWs.Cells(4, Rng.Column).Value
dWs.Cells(rowx, 2).Value = sWs.Cells(Rng.Row, 2).Value
dWs.Cells(rowx, 3).Value = sWs.Cells(Rng.Row, 4).Value
rowx = rowx + 1
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <> rng1
End If
End With
dWs.Range("A:C").Sort Key1:=dWs.Range("A2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.ScreenUpdating = True
Application.StatusBar = False
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
MsgBox ("Please proceed with data sorting !!")
End Sub


What could have possibly gone wrong ?


Thanks

 

[VoG]

I can assign that code to a button with no problem.

 

[Ron99]

Hi,

I have somehow managed to add this module, I have a problem,

The code should select "GSC ACTIVITIES" sheet when I click the button, is there anyway you can help me.

Initially the button was on the same sheet ( "GSC ACTIVITIES ") so I could execute, since I have shifted the button two sheets away, its giving me a blank data, I want the code to recognize the GSC ACTIVITIES sheet and then execute the code.

I am copying the code again

Sub Macro1()
'
' Macro1 Macro
'
'
Dim LR As Long, _
LC As Long, _
dWs As Worksheet, _
sWs As Worksheet, _
rowx As Long, _
Rng As Range, _
rng1 As String

Set sWs = ActiveSheet
Sheets.Add After:=sWs
Set dWs = ActiveSheet
dWs.Name = "Pending Task"
rowx = 2
Application.ScreenUpdating = False
LR = sWs.Range("A" & Rows.Count).End(xlUp).Row
LC = sWs.Cells(1, Columns.Count).End(xlToLeft).Column
dWs.Cells(1, 1).Value = "Name"
dWs.Cells(1, 2).Value = "Activity"
dWs.Cells(1, 3).Value = "Due Date"
With sWs.Range(sWs.Cells(5, 6), sWs.Cells(LR, LC))
Set Rng = .Find("Pending", LookIn:=xlValues)
If Not Rng Is Nothing Then
rng1 = Rng.Address
Do
dWs.Cells(rowx, 1).Value = sWs.Cells(4, Rng.Column).Value
dWs.Cells(rowx, 2).Value = sWs.Cells(Rng.Row, 2).Value
dWs.Cells(rowx, 3).Value = sWs.Cells(Rng.Row, 4).Value
rowx = rowx + 1
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <> rng1
End If
End With
dWs.Range("A:C").Sort Key1:=dWs.Range("A2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.ScreenUpdating = True
Application.StatusBar = False
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
MsgBox ("Please proceed with data sorting !!")
End Sub

 

[VoG]

Try adding the line in red

Sub Macro1()
'
' Macro1 Macro
'
'
Dim LR As Long, _
LC As Long, _
dWs As Worksheet, _
sWs As Worksheet, _
rowx As Long, _
Rng As Range, _
rng1 As String
Sheets("GSC ACTIVITIES").Select
Set sWs = ActiveSheet

 

[Ron99]

I did that, I get an error.

 

[VoG]

What error? Which line of code is highlighted if you click on Debug?

 

[Ron99]

Sheets("GSC ACTIVITIES").Select

 

[VoG]

If the error is Runtime Error 9 - Subscript out of range it means that the sheet does not exist. Check the spelling, leading or trailing spaces and so on.