Macro help

C

CBrausa

I am entering info given to me here on the Forum, I am getting an error
and this line is highlighted.

For Each myCell In myRange.Cells

I don't know VBA or Macro and am trying to figure it out on my own.

These are the formulas I have been given:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myCell As Range
Dim myRange As Range

Set myRange = Nothing
On Error Resume Next
Set myRange = Me.Range("MustFill")
On Error GoTo 0

If myRange Is Nothing Then
MsgBox "Please contact CBrausa at #### to fix the MustFill Range"
Exit Sub
End If

For Each myCell In myRange.Cells
If myCell.Address = myCell.MergeArea.Cells(1).Address Then
If myCell.Value = "" ThenApplication.EnableEvents = False
myCell.Select
Application.EnableEvents = True
Exit For
End If
End If
Next myCell

End Sub

And for the string of cells I need this to apply to:

An alternative way to setting a range is to use a macro:

Option Explicit
Sub testme()
With worksheets("sheet999") '<---
..Range("T4,E5,M5,T7,E8,M8,E14,M14,D16,M16,D18," _
& "M18,L19,M20,N21,U21,N23,B24,K29,B30,L31,B33," _
& "I33,B35,B39,B43,B45,F47,K47,P47,S47,V47,Y47,B49," _
& "T52,I53,I54,I55,H56,I58,Q50,S55,S56,S57").Nam e _
= "'" & .Name & "'!mustfill"
End With
End Sub

Change the worksheet name to match. (to match what?)

What am I doing wrong?
 
D

Dave Peterson

I don't understand why that line causes an error.

But this note:
"Change the worksheet name to match. (to match what?)"

is an instruction to change Sheet999 in this line:
With worksheets("sheet999") '<---
to the name of the worksheet that has those cells that must be filled in.
 
C

CBrausa

I changed the sheet name, reset, and it gives me the error and
highlights that line
 
G

Guest

Which line. The same one as before or "With worksheets("sheet999") '<--- " If
it is be sure you left the Quotes in the Paranthesis. It would help if you
gave us what the Error said. Also you will need to delete the space in the
line & "T52,I53,I54,I55,H56,I58,Q50,S55,S56,S57").Nam e _ Change Nam e to Name
 
C

CBrausa

The error message reads:
Compile Error:
Only comments may appear after End Sub, End Function, Or End Property.

I highlighted the line in Red.


Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myCell As Range
Dim myRange As Range

Set myRange = Nothing
On Error Resume Next
Set myRange = Me.Range("MustFill")
On Error GoTo 0

If myRange Is Nothing Then
MsgBox "Please contact CBrausa at #### to fix the MustFill Range"
Exit Sub
End If

For Each myCell In myRange.Cells
If myCell.Address = myCell.MergeArea.Cells(1).Address Then
If myCell.Value = "" Then
Application.EnableEvents = False
myCell.Select
Application.EnableEvents = True
Exit For
End If
End If
Next myCell

End Sub
 
D

Dave Peterson

You may have highlighted the line in red, but in a plain text newsgroup like
this, it can't be seen.

Make sure that there are no extra characters after the "End Sub" line.
 
C

CBrausa

I hit the delete key after End Sub to make sure there was nothing
there.

The line I typed in Red reads:
For Each myCell In myRange.Cells

How do I know if the Macro took? Where do I look? There are two
windows in the VBA and I put:

Option Explicit
Sub testme()
With worksheets("Product Quote") '<---
..Range("T4,E5,M5,T7,E8,M8,E14,M14,D16,M16,D18," _
& "M18,L19,M20,N21,U21,N23,B24,K29,B30,L31,B33," _
& "I33,B35,B39,B43,B45,F47,K47,P47,S47,V47,Y47,B49," _
& "T52,I53,I54,I55,H56,I58,Q50,S55,S56,S57").Nam e _
= "'" & .Name & "'!mustfill"
End With
End Sub

in one of the windows.
Any way I still get the error message.
 
D

Dave Peterson

I still don't have a guess why that line failed.

I hit the delete key after End Sub to make sure there was nothing
there.

The line I typed in Red reads:
For Each myCell In myRange.Cells

How do I know if the Macro took? Where do I look? There are two
windows in the VBA and I put:

Option Explicit
Sub testme()
With worksheets("Product Quote") '<---
Range("T4,E5,M5,T7,E8,M8,E14,M14,D16,M16,D18," _
& "M18,L19,M20,N21,U21,N23,B24,K29,B30,L31,B33," _
& "I33,B35,B39,B43,B45,F47,K47,P47,S47,V47,Y47,B49," _
& "T52,I53,I54,I55,H56,I58,Q50,S55,S56,S57").Nam e _
= "'" & .Name & "'!mustfill"
End With
End Sub

in one of the windows.
Any way I still get the error message.
Click to expand...
 
C

CBrausa

OK.
I've re-entered everything and it will work up to cell M16 then it
quits and lets me fill in any cell I want.

Now what?
 
C

CBrausa

Now I went in and corrected the cell numbers that weren't showing and
now it doesn't work.

I've re-entered and still doesn't work. In the past when I made a
change it ask me a question as to if I was sure I wanted to proceed,
now it doesn't. ?????
 
G

Guest

What is the Macro susposed to do exactly. Why is it only working with cells
T4,E5,M5,T7,E8,M8,E14,M14,D16,M16,D18 etc. What is in Cell M16. We will
need more information to have any clue as to what it does. Also make sure
you scroll down all as far as you can to see if there isn't anything beyond
the End Sub Lines. Something may have gotten lost down there. Did you get
the sub from this forum or from a Co-worker.

Also as a question I have from someone more knowledgable. What exactly does
Option Explicit do. I see that in a lot of code but I've never used it.
Yeah. Well helpfully there is a little information you can provide that will
allow us to give you any suggestions.
 
D

Dave Peterson

Select your worksheet
Edit|goto
type in: MustFill

Are all the cells that you need selected?

I ran the code to name the range, then did this and got all 44 cells. Did you?
 
D

Dave Peterson

"Option Explicit" forces the developers to declare their variables.

If a variable isn't declared, the procedure won't run. It's a nice way to get
the intellisense feature from the VBE and to make sure you don't have small
typos that are very difficult to find.
 
C

CBrausa

I got the formulas from the forum.

I have a form, I want certain cell to be mandatory fill in. They total
44 in all.
I think it was Conditional Formatting I tried to enter this in and it
would only take up to 25, so the forum suggested I do a Macro. Which
is what I am attempting, don't know how but am trying.

For some reason when I get it to work if I make a change, like
correcting some cell numbers, then it doesn't work. Also I'm currious
as to why when it came to the last cell and I tabbed it took me to a
cell that was not on my mandatory cell list. Why?
 
D

Dave Peterson

If you change any of the addresses, you'll have to run that macro that assigns
the name, too.

And what should happen if the all the mandatory cells are filled?

If you go to (say) the first cell of the mandatory range, how would the user be
able to change any value?

===
And if all you want is for the user not to be able to change any cell except
those 44, you can lock all the cells on the worksheet and then unlock these 44.
Format|cells|Protection tab.

Then protect the worksheet. Tools|Protection|protect sheet.
 
C

CBrausa

Dave-
I'm not sure how I've made it work, so far it accepts all but 6 cells
I have retyped them upper, or lower case and it will not take Cell
I52,I53,I54,I55,I56,I58.
I went is to see if there was a conditional format in those cells
nothing.
What should I try now?
I do appreciate all of your help. I wouldn't have gotten this fa
without it.
Thanks,



Option Explicit
Sub testme()
With Worksheets("Product Quote") '<---
.Range("T4,E5,M5,T7,E8,M8,d14,M14,D16,M16,D18,M18,m19,M20,m21,U21,N23,b25,K29,B30,L31,B33,I33,B35,B39,B43,B45,F47,K47,P47,S47,V47,Y47,B49,I52,I53,I54,I55,I56,I58,Q50,S55,S56,S57").Nam
_
= "'" & .Name & "'!MustFill"
End With
End Su
 
D

Dave Peterson

I copied your message and cleaned up the text wrapping problem and inserted the
leading dot in front of .range("

And it worked fine for me.

Could you try it on a brand new test workbook to see if that works?
 
C

CBrausa

What could be wrong that cells I52,I53,I54,I55,I56,I58 are skipped when
tabbing and entering the mandatory cells?
I have retyped, recopied,



Option Explicit
Sub testme()
With Worksheets("Product Quote") '<---
..Range("T4,E5,M5,T7,E8,M8,d14,M14,D16,M16,D18,M18,m19,M20,m21,U21,N23,b25,K29,B30,L31,B33,I33,B35,B39,B43,B45,F47,K47,P47,S47,V47,Y47,B49,I52,I53,I54,I55,I56,I58,Q50,S55,S56,S57").Name
_
= "'" & .Name & "'!MustFill"
End With
End Sub

I even tried adding "&"

Option Explicit
Sub testme()
With Worksheets("Product Quote") '<---
..Range("T4,E5,M5,T7,E8,M8,d14,M14,D16,M16,D18,M18,m19,M20,m21,U21,N23,b25,K29,B30,L31,B33,I33,B35,B39,B43,B45,F47,K47,P47,S47,V47,Y47,B49,"
& "I52,I53,I54,I55,I56,I58," & "Q50,S55,S56,S57").Name _
= "'" & .Name & "'!MustFill"
End With
End Sub


Those 6 cells are driving me nuts.
Any suggestions?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cells to be mandatory fill have a formula in it 1
Cell to be mandatory fill in 13
Please help with VBA code 12
Macro won't run correctly when a worksheet is hidden 0
Macro for Data Mining 6
Macro Creates Mult Sheets Based on Summary 2
Need Help With Macro to set a value in new sheets 2
A Macro for Multiple Sheets that Reference one Summary Page 1

Top