Author Topic: Excel and VBA Question  (Read 11552 times)

0 Members and 1 Guest are viewing this topic.

ML

  • Guest
Excel and VBA Question
« on: November 19, 2007, 01:22:44 PM »

I know this is for "ACAD" but I realize a lot of people on here do program with Excel as well; me being one of them
So I was wondering if anyone could answer this question for me?

I have macros, that read from very specific rows however one thing I never looked in to (and now need to) was; if the user inserts a new row, how can my macros dynamically adjust to the insert?

Here is a small example of something I did
Code: [Select]
Dim i As Integer

On Error Resume Next
'Check for unpaid bills
With Sheets(2).Select
 For i = 6 To 22
  If Range("N" & i).Value > 0 Then
   If MsgBox("Do you want to overwrite last week's bills?", vbQuestion + vbYesNo, "Checking Accounts") = vbYes & vbCrLf _
    Then GoTo Continue Else
     Range("L6").Select
     Exit Sub
   End If
 Next i
End With

Now if the user inserts a row, my range would now need to be 6 to 23.

Is there a way to do this in VBA?

Thank you

Mark

ML

  • Guest
Re: Excel and VBA Question
« Reply #1 on: November 19, 2007, 01:24:50 PM »

Or if it was written like this:

Code: [Select]
With Sheets(2).Select
  If Range(N6:N22).Value > 0 Then
   If MsgBox("Do you want to overwrite last week's bills?", vbQuestion + vbYesNo, "Checking Accounts") = vbYes & vbCrLf _
    Then GoTo Continue Else
     Range("L6").Select
     Exit Sub
   End If
 Next i
End With



Guest

  • Guest
Re: Excel and VBA Question
« Reply #2 on: November 19, 2007, 02:10:17 PM »
Now if the user inserts a row, my range would now need to be 6 to 23.

Is there a way to do this in VBA?

Thank you

Mark

I would probably use a named range: select some cells and click on INSERT -- NAME -- DEFINE and enter a name.  And just to test it, add a hyperlink to the newly created range.  Then click on the link - you'll see the range of cells highlighted.  Now add a new row and then click on the link again.  You'll see that the highlighted cells now include the inserted row.

ML

  • Guest
Re: Excel and VBA Question
« Reply #3 on: November 19, 2007, 02:18:05 PM »

Matt

I don't hink that will adjust the macro in VBA.
Plus I don't want to take the chance of the named range changing either.

I need a way to increment the code using rows

For example.

If User Inserts a row
 Then row that has a macro using it +1
End If

There is an AllowRowInsert Method but I don't want to do that to the user
They "should" not be limited like that; so I need to think more dynamically

Thank you

Mark


Guest

  • Guest
Re: Excel and VBA Question
« Reply #4 on: November 19, 2007, 02:21:30 PM »

Matt

I don't hink that will adjust the macro in VBA.
Plus I don't want to take the chance of the named range changing either.

I need a way to increment the code using rows

For example.

If User Inserts a row
 Then row that has a macro using it +1
End If

There is an AllowRowInsert Method but I don't want to do that to the user
They "should" not be limited like that; so I need to think more dynamically

Thank you

Mark



It won't adjust the code - and you don't need it to if you use a named range.  And the only way it could get renamed is if someone actually went through the motions of renaming it - if they even knew how to.

Could you post a sample XLS file?

ML

  • Guest
Re: Excel and VBA Question
« Reply #5 on: November 19, 2007, 02:26:52 PM »

Sorry Matt

I respectfully disagree

I do think the code needs to be adjusted and I know it can be.

Possibly if I use a
Do While Loop

Hummmm
I need to look more into it

Thanks

Mark

hendie

  • Guest
Re: Excel and VBA Question
« Reply #6 on: November 19, 2007, 02:38:26 PM »
fwiw I have to agree with Matt, named ranges are the way to go here. That's about the only way you can account for users changing/adding/deleting rows.
Changing a named range isn't something you average user would know how to do

ML

  • Guest
Re: Excel and VBA Question
« Reply #7 on: November 19, 2007, 02:48:44 PM »

Hey Hendie

How are you?

Wait a minute; I think I missed something that Matt said

If I name a range and insert a row, the named range will pick up the inserted row; or there is a way to make it do so?

Thank you,

Mark

Guest

  • Guest
Re: Excel and VBA Question
« Reply #8 on: November 19, 2007, 03:03:26 PM »
If I name a range and insert a row, the named range will pick up the inserted row...

Yup... The named range will auto-magically detect the inserted row.  Give it a shot!

ML

  • Guest
Re: Excel and VBA Question
« Reply #9 on: November 19, 2007, 03:30:59 PM »

Ok

I will try it now Matt

Thanks

Mark

Guest

  • Guest
Re: Excel and VBA Question
« Reply #10 on: November 19, 2007, 03:58:05 PM »

Ok

I will try it now Matt

Thanks

Mark

Sure.... NOW you'll try it.   :wink:

ML

  • Guest
Re: Excel and VBA Question
« Reply #11 on: November 19, 2007, 04:03:54 PM »

LOL

Now I am saying WTF!!!!!!

My spread sheet is not allowing me to define a name
It has in the past

On Worksheet activate

I set allowinsertrows to = false

I think this has something to do with it; so I need to work that out first Uggggggggggg

Do you have macros that point a cell range?

How are they affected when you insert a row

Thank you,

Mark

Guest

  • Guest
Re: Excel and VBA Question
« Reply #12 on: November 19, 2007, 04:12:27 PM »
Do you have macros that point a cell range?

How are they affected when you insert a row

Thank you,

Mark

You don't need a macro.  See the attached ZIP file.

Guest

  • Guest
Re: Excel and VBA Question
« Reply #13 on: November 19, 2007, 04:15:55 PM »
If you wanted to select the range via code, all you need is this: Range("list_of_items").Select  where 'list_of_items' is the name of the range.

ML

  • Guest
Re: Excel and VBA Question
« Reply #14 on: November 19, 2007, 04:21:55 PM »

Hummm

That is interesting

Under the help screen - Range Object,
I found this bit of code

While it is not precisely what I want; it is very close to what I am thinking

Mark

Code: [Select]
i = 1
    For Each a In Selection.Areas
        MsgBox "Area " & i & " of the selection contains " & _
            a.Rows.Count & " rows."
        i = i + 1
    Next a

The above code is using the count method but I am thinking of using the row insert method

Mark