Using Drop Down Menu for Macro


M

magmike

I am trying to use a single control on a row to determine which of three template rows to insert below the row the control is on. The row underneath this row is named PRIUnderNRC.

This is the macro assigned to the drop down menu and making a selection produces nothing. Any ideas on what I am doing wrong?:

Sub Nsert_PRISheetNRC_Row()

Application.Goto ThisWorkbook.ActiveSheet.Range("PRIUnderNRC"), scroll:=False

If Range("NRCSelection").Value = 1 Then
Range("Analog_NRC_Row").Copy
Selection.Insert
Application.CutCopyMode = False
Else
If Range("NRCSelection").Value = 2 Then
Range("BVE_NRC_Row").Copy
Selection.Insert
Application.CutCopyMode = False
Else
If Range("NRCSelection").Value = 3 Then
Range("PRI_NRC_Row").Copy
Selection.Insert
Application.CutCopyMode = False
End If
End If
End If
End Sub
 
Ad

Advertisements

G

GS

I am trying to use a single control on a row to determine which of
three template rows to insert below the row the control is on. The
row underneath this row is named PRIUnderNRC.

This is the macro assigned to the drop down menu and making a
selection produces nothing. Any ideas on what I am doing wrong?:

Sub Nsert_PRISheetNRC_Row()

Application.Goto ThisWorkbook.ActiveSheet.Range("PRIUnderNRC"),
scroll:=False

If Range("NRCSelection").Value = 1 Then
Range("Analog_NRC_Row").Copy
Selection.Insert
Application.CutCopyMode = False
Else
If Range("NRCSelection").Value = 2 Then
Range("BVE_NRC_Row").Copy
Selection.Insert
Application.CutCopyMode = False
Else
If Range("NRCSelection").Value = 3 Then
Range("PRI_NRC_Row").Copy
Selection.Insert
Application.CutCopyMode = False
End If
End If
End If
End Sub
If...Then
ElseIf...Then
ElseIf...Then
End If

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
C

Claus Busch

Hi Mike,

Am Sun, 1 Feb 2015 18:46:33 -0800 (PST) schrieb magmike:
I am trying to use a single control on a row to determine which of three template rows to insert below the row the control is on. The row underneath this row is named PRIUnderNRC.
try:

Sub Nsert_PRISheetNRC_Row()
Dim myRng As Range

Select Case Range("NRCSelection").Value
Case 1
Set myRng = Range("Analog_NRC_Row")
Case 2
Set myRng = Range("BVE_NRC_Row")
Case 3
Set myRng = Range("PRI_NRC_Row")
End Select

myRng.Copy ThisWorkbook.ActiveSheet.Range("PRIUnderNRC")
Application.CutCopyMode = False
End Sub

You can also put the code to the Worksheet_Change event that the code
runs automatically when you change the dropdown.


Regards
Claus B.
 
G

GS

Using Elseifs won't make any difference to how the macro functions.
Logically, Magmike's use of nested If ... Else statements is
equivalent to the structure Garry proposes. The only reason to use
ElseIfs is that you may find the code a bit easier to read.
You're wrong! Each ElseIf gets evaluated same as a Select Case!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
G

GS

I'm pretty sure I gave you this "InsertBlankRows" utility some time
ago, but perhaps in a different context than indicated in the
procedure. I'm also almost certaing it was with the context of copying
template rows to a specified row position. Here's the generic routine I
use for inserting rows above or below the active cell...


Sub InsertBlankRows(Optional Position As String)
' Inserts a specified number of rows at the location specified.
' If the Position arg is not used then the default is ActiveCell.Row.
Dim vRows As Variant, lPos As Long
Const sMsg As String = "Enter the number of rows to insert."

'Evaluate user input
On Error Resume Next
vRows = InputBox(Prompt:=sMsg, Default:=1): If vRows = "" Then Exit
Sub '//user cancels
If Not Err = 0 Or Not IsNumeric(vRows) Or Not vRows >= 1 Then Exit
Sub

'Get the position to insert
lPos = ActiveCell.Row: If Position = "Below" Then lPos = lPos + 1

'Insert the rows
ActiveSheet.Cells(lPos, 1).Resize(vRows).Insert Shift:=xlDown
End Sub

...where the caller would hold the position in its 'Tag' property of the
menuitem. The usage is typically inserting above, below, or just
increase the rows at the bottom of a defined input area. Popup menu
choices are...

"Insert rows &Above here" (Tag="Above")
"Insert rows &Below here" (Tag="Below")
-------------------------
"Add &More rows" (Tag="Add")

...where all 3 menuitems fire the same OnAction and are redirected using
an If...Then construct and passing the Tag value for the 1st 2 choices.
Whether or not 'template' rows are inserted depends on the project, but
if so then the template row is copied and inserted. A typical example
of how I do this is...


Sub AddRows()
' Inserts a specified number of rows at the location specified in the
ActionControl.Tag property
Const sSource$ = "AddRows"

Dim vRowCount As Variant, lPos&
Const sMsg$ = "Enter the number of rows to insert."

'Evaluate user input
vRowCount = 1 '//the default
On Error Resume Next
vRowCount = InputBox(sMsg, gsAPP_NAME, Default:=1): If vRowCount = ""
Then Exit Sub '//user cancels
If Not Err = 0 Or Not IsNumeric(vRowCount) Then NotifyInvalidInput:
Exit Sub

'Determine the number of rows
Select Case vRowCount
Case Is >= 1: vRowCount = vRowCount
Case Else: NotifyInvalidInput: Exit Sub
End Select 'vRowCount

'Get the position to insert
Select Case CommandBars.ActionControl.Tag
Case "Above": lPos = ActiveCell.Row
Case "Below": lPos = ActiveCell.Row + 1
Case "Add": lPos = Cells(Range("InputArea").Rows.Count, 1).Row +
Range("Hdr_Row").Row
End Select

'Insert the rows
EnableFastCode sSource
ActiveSheet.Unprotect PWRD
With Range("BlankTransaction")
.EntireRow.Hidden = False: .Copy: Cells(lPos,
1).Resize(vRowCount).Insert Shift:=xlDown: .EntireRow.Hidden = True
End With
Application.CutCopyMode = False
'Reset the scroll area to include the new rows
SetupUI Wks:=ActiveSheet: EnableFastCode sSource, False: wsProtect
End Sub 'AddRows

...where template rows are hidden by default so they don't clutter the
UI workspace. These are located above the top visible row. In your
scenario you can go this way for simplicity in the "'Insert the rows"
section above...


'Insert the rows
With Rows(Range("NRCSelection").Value)
.EntireRow.Hidden = False: .Copy
Cells(lPos, 1).Resize(vRowCount).Insert Shift:=xlDown
.EntireRow.Hidden = True
End With
Application.CutCopyMode = False

...where I deleted the lines specific to my own program features. This
means you don't need to evaluate the contents of "NRCSelection" if
selection is by DV list!

Note that I use local scope defined names by default because they're
reusable on other sheets, and so is what I recommend for your ranges.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
G

GS

Sorry for my lack of clarity...

Not disputing your assertion to 'functionality', but to this
statement...

"The only reason to use ElseIfs is that you may find the code a bit
easier to read."

...where my focus is on the words "only reason"! Both "If" and "Else"
are statements that need to be evaluated, and so are their respective
"End If" statements. An "ElseIf" statement only requires 1 evaluation,
and the entire construct requires only 1 "End If". In this context
these are greater priority reasons for using "ElseIf" than the added
clarity/brevity they also render! <IMO>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Ad

Advertisements

G

GS

For clarity...
..where all 3 menuitems fire the same OnAction"
Note that the example is copy/paste from a real world app that handles
the 1st 2 menuitems directly. The 3rd menuitem has its own routine!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 

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


Top