1

I have created an excel worksheet, lets call it 'pricing'. On pricing there is a command button 'notes' which when pressed will a) hide 'pricing' and b) make visible and select a second worksheet called '-notes-'. The user can then make notes and then to return the pricing sheet by pressing a command button 'back' on the -notes- sheet. The back button needs to a) hide the -notes- sheet and b) make visible and select pricing sheet.

Some of the workbooks that 'pricing' will be copied into will not have a -notes- sheet so the 'notes' button on the pricing sheet will also need to A)check that there is a sheet called -notes- and if there isn't one, it will need to create it and B) it will need to create the 'back' button and the code so when the back button is pressed, will hide -notes- and then make visble and select 'pricing'. It is this B that I am having trouble with. I can create the button and name it 'Back', but I don't know how to get it to hide -notes- and make visible and select 'pricing'.

Here is the code that checks for the -Notess- sheet and creates it if there isn't one:

Public Sub notesButton_Click()  

Dim s As Worksheet
Dim sName As String
sName = ActiveSheet.Name
Dim result As Boolean
result = False  


For Each s In ThisWorkbook.Sheets
If s.Name = "-notes-" Then 
result = True
End If
Next  


If result = False Then
Sheets.Add.Name = "-notes-"
Worksheets(sName).Visible = xlSheetVeryHidden
Worksheets("-notes-").Buttons.Add(500, 1, 81, 39).Select
Selection.Name = "Back"
Selection.OnAction = "Back"
ActiveSheet.Shapes("Back").Select 
Selection.Characters.Text = "Back"
Worksheets("-notes-").Shapes("Back").OnAction = "!<wsclose>"
Else
Worksheets("-notes-").Visible = True
Worksheets("-notes-").Select
Worksheets(sName).Visible = xlSheetVeryHidden
End If

The line:

Worksheets("-notes-").Shapes("Back").OnAction = "!<wsclose>"

is the issue. I am trying to get the button to run this sub when clicked:

Public Sub wsclose()
Worksheets(sName).Visible = True 
Worksheets(sName).Select
Worksheets("-notes-").Visible = xlSheetVeryHidden
End Sub

however, when I click the back button i get the following message:

'Cannot run the macro "Renewal Oricing Sheet v3.xls'!wsclose'. The macro may not be available in this workbook or all macros may be disabled'

Community
  • 1
  • 1
Braide
  • 155
  • 3
  • 3
  • 13
  • "*pricing sheet name is liable to change so must be dynamic*" This may be helpful: http://stackoverflow.com/a/24183859/119775 – Jean-François Corbett Aug 07 '14 at 11:02
  • "*What I am struggling with is how to get the Back command button to run the following three lines of code*" Is this your question? How to make a button run code? You got your "Notes" button to run code, using `Private Sub notesButton_Click()`... How is the "Back" button any different? – Jean-François Corbett Aug 07 '14 at 11:08
  • I know how to make the button run code by typing in the onclick event. But the notes sheet and back button is going to be created dynamically by the user clicking the notes button and therefore, the code for the back button needs to be defined within the Notes_OnCLick event. I dont know how to trigger the Back_OnCLick event from the notes_OnClick event so yes, this is essentially my question. – Braide Aug 07 '14 at 11:38
  • I'd like to help, but I don't understand your question, probably due to the many extraneous details. I suggest you rewrite it, keeping only the essential bits. – Jean-François Corbett Aug 07 '14 at 15:42
  • HiJean, I re wrote the question and am still in need of an answwer if you are able to take another look? – Braide Aug 14 '14 at 09:45

0 Answers0