Small amendment needed in code

Ironman

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
Hi

The below code (which I didn't write) includes a data validation entry.

Right now, the data validation applies to whichever sheet I run the macro in.

What I'm after is for the data validation code to only run in sheet1.

Code: 
Sub Fillcell(Worksheet As Integer, color As Integer, cValue As String)

Application.EnableEvents = False
If Worksheet = 2 Or 14 Then
With Selection
.Font.Name = "Wingdings"
.Font.Size = 12
.Font.ColorIndex = 1
.Value = cValue
With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = False
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = "Double click for lifetime mileage total up to this date"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End With
End If

With Selection.Interior
.ColorIndex = color
.Pattern = xlSolid
End With

Application.EnableEvents = True
End Sub

Many thanks!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Why not Surround your Entire Code lines with:

If Activesheet.name = "Sheet1" then

Your existing code....

End If (Just above END SUB
 
Upvote 0
This is untested, but I think it should work. Note I change the argument list and removed the Worksheet argument since you only want this to work on Sheet1.
Code: 
[table="width: 500"]
[tr]
	[td]Sub Fillcell(Color As Long, cValue As String)

  If ActiveSheet.Name = "Sheet1" Then
    
    Application.EnableEvents = False
  
    With Selection
      .Font.Name = "Wingdings"
      .Font.Size = 12
      .Font.ColorIndex = 1
      .Value = cValue
      With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = False
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = "Double click for lifetime mileage total up to this date"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
      End With
    End With
  
    With Selection.Interior
      .ColorIndex = Color
      .Pattern = xlSolid
    End With
  
    Application.EnableEvents = True
  
  End If
  
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Many thanks Jim and Rick - I should have made it clearer that all of the following code needs to run in all of the sheets:

Code: 
Sub Fillcell(Worksheet As Integer, color As Integer, cValue As String)
Application.EnableEvents = False
If Worksheet = 2 Or 14 Then
With Selection
.Font.Name = "Wingdings"
.Font.Size = 12
.Font.ColorIndex = 1
.Value = cValue

End With
End If

With Selection.Interior
.ColorIndex = color
.Pattern = xlSolid
End With

Application.EnableEvents = True

Re the line
Code: 
If Worksheet = 2 Or 14 Then
Right now, the macro isn't restricted to Sheets 2 and 14, so I don't know what that row of code is doing.

The data validation below is what must only run in Sheet1:

Code: 
With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = False
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = "Double click for lifetime mileage total up to this date"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Thanks again.
 
Last edited:
Upvote 0
Ironman said:
Re the line
Code: 
If Worksheet = 2 Or 14 Then
Right now, the macro isn't restricted to Sheets 2 and 14, so I don't know what that row of code is doing.
Click to expand...
That line of code was incorrectly formed, it should have read this way...
Code: 
If Worksheet = 2 Or Worksheet = 14 Then




Ironman said:
Many thanks Jim and Rick - I should have made it clearer that all of the following code needs to run in all of the sheets:

The data validation below is what must only run in Sheet1:
Click to expand...
Does this version do what you want (I have still removed the Worksheet argument as you will not need it if I understood your request correctly)...
Code: 
[table="width: 500"]
[tr]
	[td]Sub Fillcell(Color As Long, cValue As String)

    Application.EnableEvents = False
  
    With Selection
      .Font.Name = "Wingdings"
      .Font.Size = 12
      .Font.ColorIndex = 1
      .Value = cValue
      
      If ActiveSheet.Name = "Sheet1" Then
        With Selection.Validation
          .Delete
          .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
          :=xlBetween
          .IgnoreBlank = True
          .InCellDropdown = False
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = "Double click for lifetime mileage total up to this date"
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
        End With
      End If
    
    End With
  
    With Selection.Interior
      .ColorIndex = Color
      .Pattern = xlSolid
    End With
  
    Application.EnableEvents = True
  
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Hi Rick, many thanks for that.

I've just run that macro and it's not the one I thought it was - it errored "wrong number of arguments or invalid property assignment" - I then realised Fillcell code is below the following in the worksheet - what I requested is what I want to happen when FillGreen, FillYellow, FillAmber and FillRed macros run
Code: 
Sub FillGreen()
If ActiveSheet.Name = "Analysis" And ActiveCell.Column = 6 Then
Fillcell 1, 4, "J"
ElseIf ActiveSheet.Name = "Training Log" And ActiveCell.Column = 7 Then
Fillcell 2, 4, "J"
ElseIf ActiveSheet.Name = "Exercise Bike" And ActiveCell.Column = 9 Then
Fillcell 4, 4, "J"
ElseIf ActiveSheet.Name = "Cycling" And ActiveCell.Column = 7 Then
Fillcell 4, 4, "J"
ElseIf ActiveSheet.Name = "Walking" And ActiveCell.Column = 6 Then
Fillcell 4, 4, "J"
Else
MsgBox "Cell fill does not work in this sheet", vbInformation, "Info"
End If
End Sub

Sub FillYellow()
If ActiveSheet.Name = "Analysis" And ActiveCell.Column = 6 Then
Fillcell 1, 6, "K"
ElseIf ActiveSheet.Name = "Training Log" And ActiveCell.Column = 7 Then
Fillcell 2, 6, "K"
ElseIf ActiveSheet.Name = "Exercise Bike" And ActiveCell.Column = 9 Then
Fillcell 4, 6, "K"
ElseIf ActiveSheet.Name = "Cycling" And ActiveCell.Column = 7 Then
Fillcell 4, 6, "K"
ElseIf ActiveSheet.Name = "Walking" And ActiveCell.Column = 6 Then
Fillcell 4, 6, "K"
Else
MsgBox "Cell fill does not work in this sheet", vbInformation, "Info"
End If
End Sub

Sub FillAmber()
If ActiveSheet.Name = "Analysis" And ActiveCell.Column = 6 Then
Fillcell 1, 44, "K"
ElseIf ActiveSheet.Name = "Training Log" And ActiveCell.Column = 7 Then
Fillcell 2, 44, "K"
ElseIf ActiveSheet.Name = "Exercise Bike" And ActiveCell.Column = 9 Then
Fillcell 4, 44, "K"
ElseIf ActiveSheet.Name = "Cycling" And ActiveCell.Column = 7 Then
Fillcell 4, 44, "K"
ElseIf ActiveSheet.Name = "Walking" And ActiveCell.Column = 6 Then
Fillcell 4, 44, "K"
Else
MsgBox "Cell fill does not work in this sheet", vbInformation, "Info"
End If
End Sub

Sub FillRed()
If ActiveSheet.Name = "Analysis" And ActiveCell.Column = 6 Then
Fillcell 1, 3, "L"
ElseIf ActiveSheet.Name = "Training Log" And ActiveCell.Column = 7 Then
Fillcell 2, 3, "L"
ElseIf ActiveSheet.Name = "Exercise Bike" And ActiveCell.Column = 9 Then
Fillcell 4, 3, "L"
ElseIf ActiveSheet.Name = "Cycling" And ActiveCell.Column = 7 Then
Fillcell 4, 3, "K"
ElseIf ActiveSheet.Name = "Walking" And ActiveCell.Column = 6 Then
Fillcell 4, 3, "K"
Else
MsgBox "Cell fill does not work in this sheet", vbInformation, "Info"
End If
End Sub

My sincere apologies for unintentionally misleading you there - would you mind adapting what you wrote so this will still run please?

Many thanks again.
 
Last edited:
Upvote 0
Ironman said:
I've just run that macro and it's not the one I thought it was - it errored "wrong number of arguments or invalid property assignment"
Click to expand...
You got that error because I removed the first argument for the FillCell subroutine (because, with the exception of the validation part, you no longer care what sheet is active when you call it); however, each one of your calls to the FillCell subroutine still pass a sheet number. You need to remove the first argument from each existing line of code that calls the FillCell subroutine. For example, you have this right now...

Fillcell 1, 4, "J"

You need to remove the first argument and change the call to the FillCell subroutine to this....

Fillcell 4, "J"

And you have to do this deletion of the first argument to each line of code like the one above.
 
Upvote 0
Hi Rick, you're absolutely right, that works fine now.

The only thing is the data validation part doesn't run in Sheet 1 when I run any of the Fill colour macros.
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question
recorded macro throwing 1004 error
Replies
3
Views
384
ursua
U
D
  • Solved
Add/clear drop down (data validation) based upon a cell's content
Replies
7
Views
1K
dmj120
D
J
  • Question
Combining VBA code together...need help!
Replies
3
Views
193
Fluff
Fluff
J
  • Question
Data Validation list that pulls from multiple columns in active row?
Replies
7
Views
571
Peter_SSs
Peter_SSs
V
  • Question
Excel VBA to Create Data Validation List from Range with Filter
Replies
1
Views
814
RoryA
RoryA

Forum statistics

Threads
1,215,480
Messages
6,125,048
Members
449,206
Latest member
Healthydogs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top