Creating a custom workbook

Hi Good morning people (UK)

I'm Matt, from the United Kingdom, not a total excel "noob" I have managed to do some fairly basic workbooks up until now!.

For my next project I want to enlist the help of the "hive mind" with a project I am working on.

I want to create a workbook in excel that once opened displays a dialogue box asking how many sheets the user requires and also the option to amend this at a later time by reproducing that box from a check button somewhere on the first sheet. The sheets reproduced with all be to set standard which I will create.

Is this at all possible in excel?

Regards

Matt

Thx for replying promptly,

I ideally want to keep everything in one file for ease of use, so if i share the document for other to use they don't have to install extra files. What other information are you after? please bare in mind i am a complete laymen to VB but i am willing to learn.

Matt

Hi matt,
Sorry if I'm not helping here.
I just want to know more what is your situation.

Originally Posted by mattsporter

I want to create a workbook in excel

Did you mean that a macro within an Excel Workbook to create a new workbook ?

But from your next quote, then...

that once opened displays a dialogue box asking how many sheets the user requires

Then I think you want to create a sheet in a workbook, write a macro in this workbook,
where the macro is to delete/add a sheet in this workbook ? For example :
you write that macro in sample.xlsm where there are 10 sheets in this sample.xlsm
What you want is :
When this sample.xlsm is open, it ask how many sheets the user requires.
If for example the user's answer is 7, then there will be 3 sheets is deleted (or hide ?)
If for example the user's answer is 15, then there will be 5 new sheets is added.

Sorry if I'm not getting your situation.

Yes apologise if my question is vague.

I want to create a workbook where everything is contained within one file. The work book would be used for LEV testing Local Exhaust Ventilation. at the moment we have only one word document pretty well laid out but everything is manual doing all the calculations and dimensions etc.

Me being me, i'm quite lazy when i know there is better way to do something and make my work life easier i'll opt for that road.

The work book would contain a front page explaining the system being tested, then a second page with a schamatic drawing and the then i want a button i can press to generate how ever many pages required which would be for individual capture points. The pages that it would generate are designed by me as well.

hope this helps

Regards

Matt

Originally Posted by karmapala

Hi matt,
Sorry if I'm not helping here.
I just want to know more what is your situation.


Did you mean that a macro within an Excel Workbook to create a new workbook ?

But from your next quote, then...


Then I think you want to create a sheet in a workbook, write a macro in this workbook,
where the macro is to delete/add a sheet in this workbook ? For example :
you write that macro in sample.xlsm where there are 10 sheets in this sample.xlsm
What you want is :
When this sample.xlsm is open, it ask how many sheets the user requires.
If for example the user's answer is 7, then there will be 3 sheets is deleted (or hide ?)
If for example the user's answer is 15, then there will be 5 new sheets is added.

Sorry if I'm not getting your situation.

What i did want was an input box to open as soon as the document opens asking for how ever many sheets needs to be made. But now looking into VB i think a Button on the top of the first page would be a better idea.

I have managed to create a button and get it to function so when it is pressed a input box opens asking how many capture points are required. but this is as far as i have got for now.

Regards

Matt

Originally Posted by mattsporter

I have managed to create a button and get it to function
so when it is pressed a input box opens asking how many capture points
are required. but this is as far as i have got for now.

Something like this ?

Please Login or Register  to view this content.

The sub will create a new sheet as many as the user's answer in the inputbox.

Thank you Karmapala

This has worked to an extent, but when the sheets are created the always start before sheets 1. and when i delete them and recreate them it doesnt start form 2 again it will start from 13 or 14 or how ever many i created before.

Regards

Matt

Originally Posted by Keebellah

What's in that file?

The file will be a test report, so air flow readings, duct velocities, photographs, and schematic drawings of the system tested.

Public Sub testAddSheets()
Dim myAnswer As Variant
Dim x As Integer
Dim s As Integer
Dim p As Integer
Dim n As Integer
Dim i As Integer
Dim ws As Worksheet
Dim wsN As Worksheet
myAnswer = Application.InputBox("type how many sheets you require", Type:=1)
x = CInt(myAnswer)
If x = 0 Then Exit Sub
For Each ws In Worksheets
If Left(ws.Name, 9) = "NewSheet-" Then
p = WorksheetFunction.Max(p, Val(Right(ws.Name, 2)))
End If
Next ws
For i = 1 To x
p = p + 1
If p = 1 Then
Sheets.Add After:=Sheets(1)
ActiveSheet.Name = "NewSheet-" & Format(p, "00")
Else
Sheets.Add After:=Sheets("NewSheet-" & Format(p - 1, "00"))
ActiveSheet.Name = "NewSheet-" & Format(p, "00")
End If
Next i

End Sub

I have a dedicated sheet set up for a capture point, rather than creating a blank sheet from from this code can the sheet i created be made instead?

I'm sorry as I don't understand your situation.

Originally Posted by mattsporter

I have a dedicated sheet set up for a capture point,
rather than creating a blank sheet from from this code can the sheet i created be made instead?

displays a dialogue box asking how many sheets the user requires

Who is going to create the sheet ?
Is it you ? or someone else ?

I thought the situation is like this :
There is a workbook with you.
This workbook will be shared to someone else.
What you want is that someone else who open the workbook can just click a button (with a macro you've made before hand which you ask in this forum now), enter the number on how many sheets that someone else requires.

But it seems what I thought is not correct,
because it seems that it's only you who will click that button ?

Also, I think you haven't told us how you want the created sheet to be named after you enter the number of sheets required ?

Sorry for my confusion.

@Keebellah,

Yes, better wait the OP answer with more detail of his situation.

Thank you Keebellah .

Hi There,

sorry about the confusion, i thought i'd been as clear as i possibly could. I have attached a sample doc for you to look at.

As i asked before, i wanted to a be able to generate as many capture point sheets as the user required.

Originally Posted by Keebellah

blank template and is copied, what's the name do you want to give these new sheets? CapturePoint-001 -002 etc? or an entirely other name?

Capture point 1, Capture point 2, etc..

the Blank template you are talking about, can this also have the formulas in it as well for calculations.
Would they be generated every time someone decided to say, generate 5 capture point pages?

No worries, when i pressed the generate button a new capture point template is made and so is the formulas which is great. thank you there is more code there then i could have imagined.

i tried to hide the master capture point template as i don't want this been seen so only the ones generated are seen but when i hide it they all seem to hide as well. is this something i can do? or is it down to programming again?

Hi Keebellah

Is it possible with a formula, (and i have looked in excel and on youtube) to put a drop down list above that 7x8 grid and have a selection list of either circle,grid,oval so then the user can select either pattern?

Regards

Matt

Yes depending on the system being tested.

If someone is testing a circular duct, then they can select a the circle and square for grid and so on.

Once the user works with it.

I want to be able to change it with the option of a drop down list if possible.

Originally Posted by Keebellah

Okay, but will the capture points all be for one type in one and the same workbook or will if be different Capture points?
What I mean to say is that you can post the question as now, how many Capture points and then the next one will be make a choice for either Circle, Grid or Oval and all the new worksheets will be set for that,
Option 1: all new sheets with no specific choice, blank square
Option 2: all new sheets for one type of capture point (all x the same)
Option 3: prompt for each new sheet with a choice 0-3 where 0 is blank, 1 = circle, 2= grid 3 = oval

Option 3, I want to be able to create x amount of capture point sheets and then in some sort of drop down list choose which ever grid reference I want depending on what I am testing.

Originally Posted by Keebellah

That means you want to do do that independently AFTER the new sheers have been created? That will mean that the Caption point sheets will need event trigger like right click or double click in for example the are above the square and show an input form to enter a number between 0 and 3 included to 'populate' the area below that the range G20:M27

How do I do that then?

Thank you, that is some great work.

Is it possible to assign the Ctrl - x to a button I have placed in the capture form.

Also is it possible when you create a capture sheet the number corresponding to that sheet e.g. (Capture point 1) also gets added to the top left corner of that sheet where it says capture point.

Regards

Matt.

I'll give that a go, thank you, you've been a great help.

Apologises, I had already tried what you'd suggested before i asked. but now i see the issue. I was attempting to test the macro on the template but it wasn't working, i created a capture point sheet and retested and found that it now does work. so thank you. My mistake.

I have ordered a VBA for dummies as well to help me pick on VBA as quick as i can.

One thing I am having an issue with is, when i lock down the sheets to protect the form, i cannot produce a new grid. keeps informing me that the sheet is locked. I have tried multiple ways around this, is there something that i can do as a work around?

Originally Posted by Keebellah

I suggest you check-out the different tutorials where you can get scraps of vba code snippets to test.
The best way is like we all started, record a macro and then edit it and make it do more.
Some very important ingredients are Time, patience and imagination, I started like that no courses, jus doing it; but that was in and around 1995 when VBA was not fully implemented yet
Let me know if you got the numbering working

I have been pulling bits of code from the internet to help me with this as well as your help.

regarding the getting the number thing i am trying this at the moment.

Please Login or Register  to view this content.

and then using =SHEETNAME(3)

No worries, sorted the number situation out.

Please Login or Register  to view this content.

I didn't realize until today how easy it would be to fill in the wrong form and delete a formula.

That number thing does work.

Originally Posted by Keebellah

Tell me which cells are the unlocked cells in the Capture point form and do you want to lock the worksheet with a password? Then which one?

When I protect the forms I cannot creat a grid

I wanted to lock the capture point sheet

That kind of worked, but the create grid button vanished.

Question. Can all this be implemented into a actually custom program. I ask because I think it would be better than an Excel sheet.

The button that was set on the above the capture grid which i assigned a macro Ctrl + X to open the input box to select which ever grid the user wanted.

I mean can this be done as an actual standalone program from VBA?

Apologises, i had been working away.

The reason i added a button was because some people are not that good with excel, am building this for me, but i know it will get out and other people will want it, so have thinking ahead.
This is why i added a button.

to be honest i'd not had chance, but while i was away i'd been working on the form in my hotel room and managed to incorporate a equation into the capture point which should help on results.

Sorted some more formula's out and tided up the margins.

What i did want was to add another capture grid called partial enclosure. i have managed so far with this.

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

But i am struggling to see how you assigned the actual grid to the code.

here is the full code.

Please Login or Register  to view this content.

and then i edited this

Please Login or Register  to view this content.

hope this helps

here is a copy of the newest form so far with edited code,
I wanted the partial enclosure added to the capture grid. if you could tell me rather do the editing if you prefer that.

I know i requested last time to add protection for the capture sheet so only certain cells can edited.

is it possible to lock down and hide all cells that have formulas in them this would include anything from the front sheet to the capture sheet.
At the same time not cause any issues with generating a capture reference such as "Circle" "Grid" etc... i still want to be able to generate them,
and edit the cells from the capture point.

I have also found a little bit of script on here so i can press a button and generate a number in the footer of the all the pages generated.

New form attached.

Thank you, but with regards to the capture point and fletchers nomogram they cant always be used together.

You can only use fletchers when testing a receiving hood such such with an aperture of a cicrle, grid, or oval.

i think i have sorted everything out.

There is one thing i cannot find out there, I am trying to count the amount of capture point sheets and place that number in the third box down on the front page call #capture points, is there a way of doing this?

Ah right i'll point it to that

Could you take a look i have already had this working today where i had locked and hidden everything down and could still edit the capture grids and specific cells.

For some reason it's not working now, i don't know what i have done.

The systems i test are connected to fan motors, so we have to record all information about the fan, make, model, type, power, RPM, Kilowatts, AMPS, direction, etc etc... that will be the last form in this venture. hopefully.

You asked me to assign that parameter to a macro. I made a macro setting the password and then added that in there. but it wasn't working.