Get Name of Sheet to use in Macro

M

magmike

I have a macro that takes a named range (New_Row), copies it and inserts itabove the current selection. It is called by a command button. As part of the macro, it sends the focus back to the original sheet (Orders) as the template row is on a different sheet.

But now, I want to use this command button on other sheets. However, it sends the user back to Orders, instead of the present sheet. How could I retrieve the name of the current sheet and then use it in my Macro? (Code below)

Sub Nsert_TemplateRow()
Dim Msg, Style, Title, Response
Msg = "This will insert a New Row ABOVE your currently selected cell. Do you wish to continue?"
Style = vbYesNo + vbInformation
Title = "Insert Row"

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
If Selection.Column = 1 Then

Selection.Insert
Sheets("Sheet1").Select
Range("New_Row").Select
Selection.Copy
Sheets("Orders").Select
ActiveSheet.Paste

Else
MsgBox "You must first select a cell in column A", vbInformation, Title
End If
Else
End If

End Sub

Thanks in advance for your help,

magmike

PS: I am open to suggestions on a better way to write the whole of it also,but still would like to know how to pull the name of the sheet and use it elsewhere.

I had tried the following which worked in another workbook, but it will notin this one. Not sure why, but it the error says something about not beingable to insert a row (it is a themed table - could that have something to do with it?)

'Range("New_Row").Copy
'Selection.Insert Shift:=xlDown
'Application.CutCopyMode = False

msk
 
G

GS

Try...


Sub Insert_TemplateRow()
Const sMsg$ = "This will insert a New Row" _
& " ABOVE your currently selected cell." _
& vbLf & vbLf & " Do you wish to continue?"
Const lStyle& = vbYesNo + vbInformation
Const sTitle$ = "Insert Row"

If (MsgBox(sMsg, lStyle, sTitle) = vbYes) Then
Sheets("Sheet1").Range("New_Row").Copy
Cells(ActiveCell.Row, 1).Insert Shift:=xlDown
Application.CutCopyMode = False
End If 'vAns
End Sub

...which inserts the new row at the activecell.row, shifting the rows
down. You don't need to select any sheets or any particular column.
(This code assumes Range("New_Row") on Sheet1 is an entire row!)

--
Garry

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

magmike

I have a macro that takes a named range (New_Row), copies it and inserts it above the current selection. It is called by a command button. As part of the macro, it sends the focus back to the original sheet (Orders) as the template row is on a different sheet.



But now, I want to use this command button on other sheets. However, it sends the user back to Orders, instead of the present sheet. How could I retrieve the name of the current sheet and then use it in my Macro? (Code below)



Sub Nsert_TemplateRow()

Dim Msg, Style, Title, Response

Msg = "This will insert a New Row ABOVE your currently selected cell. Do you wish to continue?"

Style = vbYesNo + vbInformation

Title = "Insert Row"



Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then

If Selection.Column = 1 Then



Selection.Insert

Sheets("Sheet1").Select

Range("New_Row").Select

Selection.Copy

Sheets("Orders").Select

ActiveSheet.Paste



Else

MsgBox "You must first select a cell in column A", vbInformation, Title

End If

Else

End If



End Sub



Thanks in advance for your help,



magmike



PS: I am open to suggestions on a better way to write the whole of it also, but still would like to know how to pull the name of the sheet and use it elsewhere.



I had tried the following which worked in another workbook, but it will not in this one. Not sure why, but it the error says something about not being able to insert a row (it is a themed table - could that have something to do with it?)



'Range("New_Row").Copy

'Selection.Insert Shift:=xlDown

'Application.CutCopyMode = False



msk
Click to expand...

I get the error:

Run-time error '1004':

This operation is not allowed. It is attempting to shift
cells in a table in your worksheet.

Any ideas?
 
G

GS

I get the error:
Run-time error '1004':

This operation is not allowed. It is attempting to shift
cells in a table in your worksheet.

Any ideas?
Click to expand...

Yes.., you are trying to run the code within an area that's been
created as a 'Table Object' via the 'Data, Table' command! (Or the Data
tab, What If analysis, Data Table)

--
Garry

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

magmike

I have a macro that takes a named range (New_Row), copies it and inserts it above the current selection. It is called by a command button. As part of the macro, it sends the focus back to the original sheet (Orders) as the template row is on a different sheet.



But now, I want to use this command button on other sheets. However, it sends the user back to Orders, instead of the present sheet. How could I retrieve the name of the current sheet and then use it in my Macro? (Code below)



Sub Nsert_TemplateRow()

Dim Msg, Style, Title, Response

Msg = "This will insert a New Row ABOVE your currently selected cell. Do you wish to continue?"

Style = vbYesNo + vbInformation

Title = "Insert Row"



Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then

If Selection.Column = 1 Then



Selection.Insert

Sheets("Sheet1").Select

Range("New_Row").Select

Selection.Copy

Sheets("Orders").Select

ActiveSheet.Paste



Else

MsgBox "You must first select a cell in column A", vbInformation, Title

End If

Else

End If



End Sub



Thanks in advance for your help,



magmike



PS: I am open to suggestions on a better way to write the whole of it also, but still would like to know how to pull the name of the sheet and use it elsewhere.



I had tried the following which worked in another workbook, but it will not in this one. Not sure why, but it the error says something about not being able to insert a row (it is a themed table - could that have something to do with it?)



'Range("New_Row").Copy

'Selection.Insert Shift:=xlDown

'Application.CutCopyMode = False



msk
Click to expand...

What is the benefit of a table object?
 
G

GS

What is the benefit of a table object?

I have no idea if used only to store data. There are some advantages if
you run Scenarios because a 'Data Table' object has special features
all its own same as do 'Pivot Tables'. I know they're used heavily in
'Financial Models' but don't see any point in using them for raw data
stored in a worksheet for purposes of normal data manipulation!

But then, I use worksheets as normal database tables as so what do I
know!!!

--
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

Here's what I use to insert a specified number of rows above or below
the active cell. It's called by a common entry point routine that
passes the 'Caller.Tag' value. The menuitems are added to a custom
popup menu.


Public 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:
Application.CutCopyMode = False
End Sub

The entry point routine:
Sub AddMoreRows()
Call InsertBlankRows(CommandBars.ActionControl.Tag)
End Sub

The menuitems:
Insert rows above here (Tag:="Above", Macro:="AddMoreRows")
Insert rows below here (Tag:="Below", Macro:="AddMoreRows")

--
Garry

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

magmike

I have a macro that takes a named range (New_Row), copies it and inserts it above the current selection. It is called by a command button. As part of the macro, it sends the focus back to the original sheet (Orders) as the template row is on a different sheet.



But now, I want to use this command button on other sheets. However, it sends the user back to Orders, instead of the present sheet. How could I retrieve the name of the current sheet and then use it in my Macro? (Code below)



Sub Nsert_TemplateRow()

Dim Msg, Style, Title, Response

Msg = "This will insert a New Row ABOVE your currently selected cell. Do you wish to continue?"

Style = vbYesNo + vbInformation

Title = "Insert Row"



Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then

If Selection.Column = 1 Then



Selection.Insert

Sheets("Sheet1").Select

Range("New_Row").Select

Selection.Copy

Sheets("Orders").Select

ActiveSheet.Paste



Else

MsgBox "You must first select a cell in column A", vbInformation, Title

End If

Else

End If



End Sub



Thanks in advance for your help,



magmike



PS: I am open to suggestions on a better way to write the whole of it also, but still would like to know how to pull the name of the sheet and use it elsewhere.



I had tried the following which worked in another workbook, but it will not in this one. Not sure why, but it the error says something about not being able to insert a row (it is a themed table - could that have something to do with it?)



'Range("New_Row").Copy

'Selection.Insert Shift:=xlDown

'Application.CutCopyMode = False



msk
Click to expand...

Pop up menu? Is that like a combo drop down box?
 
G

GS

Pop up menu? Is that like a combo drop down box?

It's like the Cells right-click popup menu, which my apps replace with
a custom menu. Optionally, you can add these menuitems to the Cells
menu.

--
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

Is there a reason you keep replying to your original post *instead* of
the replies your reply relates to?

--
Garry

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

magmike

Is there a reason you keep replying to your original post *instead* of

the replies your reply relates to?



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion
Click to expand...

Sorry, I didn't realize that I was doing that.

I looked up "popup menu" in excel help and am having lots of fun. Thanks!
 
G

GS

I use the following in a module named 'mMenus' inside my Personal.xls
so it's available in any version of Excel I have open...

Option Explicit


Sub AddToShortcut()
With CommandBars("Cell")
.Controls(1).BeginGroup = True
With .Controls.Add(Type:=msoControlButton, Before:=1)
.Caption = "Insert rows below here": .OnAction = "AddMoreRows"
.Tag = "Below": .Style = 1
End With

With .Controls.Add(Type:=msoControlButton, Before:=1)
.Caption = "Insert rows above here": .OnAction = "AddMoreRows"
.Tag = "Above": .Style = 1
End With
End With
End Sub

Public 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:
Application.CutCopyMode = False
End Sub

Sub AddMoreRows()
InsertBlankRows CommandBars.ActionControl.Tag
End Sub

...and to remove the menus...

CommandBars("Cell").Reset

--
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

Changes in How Code Acts After Upgrade of Excel 2
Copying Row Code Doesn't Copy Conditional Formating 10
Using Drop Down Menu for Macro 6
Modify Code to work on Sheets with different names 5
Can Vba workout if a selection is part of a group? 0
How to get data from datatable to dataset to use in report viewer ? 1
How to record a macro within a macro 9
moving rows to another sheet 3

Top