Techy Hel: Excel .. Further to - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HUKD, you accept our cookie and privacy policy.
Get the HUKD app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

Techy Hel: Excel .. Further to

Disco Avatar
7y, 11m agoPosted 7 years, 11 months ago
my help from a couple of excel wizz's on here - cheers lads - I now need to link 300 pages (numbered sheets 1 - 300) into a front screen for easy access by the end user. (i NEED to use EXCEL as thats the software he's started it in)a

Whats the best way of going about this? thought about using macros,but then nto sure how many macros I can have per workbook, and to create 300 macro's is gonna take some time.
Disco Avatar
7y, 11m agoPosted 7 years, 11 months ago

All Comments

(4) Jump to unreadPost a comment
You may have to do a little refining of the code but this will essentially get you heading in the right direction. Create a command button on the front page called "Go to Page" or whatever label you want to give it. Change the command buttons object name to cmdGoTo and copy the code below into it.

Private Sub cmdGoto_Click()
'Declare variables required
Dim objwb As Workbook
Dim intPgCount As Integer
Dim intGotoPage As Integer


'Set object variables
Set objwb = Application.ActiveWorkbook
intPgCount = objwb.Sheets.Count

'Capture page number by prompting user
intGotoPage = InputBox("Enter Page Number to go to : Between 1 and " & intPgCount, "GoTo Page")

'Make sure the value entered is a valid sheet number
If intGotoPage < 1 Or intGotoPage > intPgCount Then
MsgBox "Error. Invalid Page Number"
Exit Sub
End If

'If we have got this far then we have a valid page number. Activate the requested page number.

'Explicit Exit sub
Exit Sub

'Exception handling. Generic.
MsgBox "Error Number " & Err.Number & " : " & Err.Description & ". Further Action Cancelled."
Exit Sub

End Sub

where the hell do I do that? lol
where the hell do I do that? lol

On the menu, click VIEW / TOOLBARS / CONTROL BOX, and a row of icons will appear, on that row there will be a little grey rectangle shape (this is the command button control). When on your front page, click on the grey rectangle, your cursor will change to a cross, this means you have to draw your button on your front page. Once thats done, right click on the button you've just drawn and then click properties. At the top of the box that appears you will see (name), to the right of that you will see commandbutton1, change the commandbutton1 to cmdGoTo. Close the properties window, right click on your button again then click VIEW CODE. Copy and paste the code from my original thread into that module.

Once thats done, go back to your front page. Look for the design mode icon (its like a ruler, a pencil on it and a triangle). click this to go between design and run mode. on run mode you should be able to click the button you've drawn and it will then ask you to go to a page number.

You can also test the code after you've pasted it into the sheet module by pressing F8 and stepping thru the code line by line.
sounds complicated like! lol

also, i would like to "create new customer" so when the button is selected, its create a new page etc -- i guess this is easy enough

Post a Comment

You don't need an account to leave a comment. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!