Jump to content

Welcome to Geeks to Go - Register now for FREE

Need help with your computer or device? Want to learn new tech skills? You're in the right place!
Geeks to Go is a friendly community of tech experts who can solve any problem you have. Just create a free account and post your question. Our volunteers will reply quickly and guide you through the steps. Don't let tech troubles stop you. Join Geeks to Go now and get the support you need!

How it Works Create Account
Photo

Excel VB Help


  • Please log in to reply

#1
Spyderturbo007

Spyderturbo007

    Member

  • Member
  • PipPipPip
  • 760 posts
This should be pretty simple, but since I know almost nothing about VB, it's driving me up a wall. I have a spreadsheet that contains Sheet1 & Sheet2. Right now, when you open the workbook, it prompts you to enter data, which is added to specific cells. I found the code online and it works well.

What I want to do is have an input box appear when you open the workbook and it ask you something like "Would you like to work with Sheet1 or Sheet2". Then when the user makes a choice, it automatically moves you to the correct sheet, and automatically starts prompting for data specific to that sheet.

I have the code for data entry, but I can't find anything, anywhere about sheet selection. Well, at least nothing that I can understand. :)

Thanks!
  • 0

Advertisements


#2
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
Hello Spyderturbo007,

Since you are already familiar with User Forms, perhaps the below information will get you started.

To get a form the open when you open the workbook, place the below code in the "This Workbook" Section within the VBA Editor window.

Private Sub Workbook_Open()
UserForm1.Show
End Sub

NOTE: UserForm1 will need to be changed to match the name of your UserForm

For the sample Userform, I used a label, and 3 Command Buttons:
  • Label: Statement that asks the user to what sheet they want to start on.
  • Sheet 1 button: Clicking this will take the user to Sheet 1
  • Sheet 2 button: Clicking this will take the user to Sheet 2
  • Close Form button: This button simply closes the form
And here is the code that needs to placed under each control.

Sheet 1 button
Private Sub CommandButton1_Click()
Sheets("Sheet1").Activate
End Sub

Sheet 2 button
Private Sub CommandButton2_Click()
Sheets("Sheet2").Activate
End Sub

Close Form button
Private Sub CommandButton3_Click()
Unload Me
End Sub

I hope this proves helpful... enjoy your weekend!

Sincerely,

dm
  • 0

#3
Spyderturbo007

Spyderturbo007

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 760 posts
Thanks for your help. I was able to get almost everything working. I have the form button working and it activates the correct sheet. The problem I'm having now, is that I can't get the calculation input boxes to open once I close the UserForm1 box.

Prior to your help, I was only able to get things to work when I had one sheet. So under the WorkBook, I had this, which would start asking for input once the file was opened. I had to have two different files, instead of having one file with multiple sheets.

Sub Workbook_Open()
	Range("B7").Select
	ActiveCell.FormulaR1C1 = InputBox("Enter Cell Phone Bill Cost")
End Sub

After combining both sheets into one file, I moved that code to Sheet1 and even tried changing it from Workbook, to Worksheet, but that didn't work.

The only way I can get that to run is by hitting Alt + F8 and then choosing the macro associated with the sheet.

Is there a way to get that to run after the user closes UserForm1?

Thanks again!

Edited by Spyderturbo007, 15 June 2010 - 07:33 AM.

  • 0

#4
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
Hello again,

Glad to hear you making some good progress. :)

A couple questions if you don't mind
  • How many sheets in the workbook now?
  • Would you mind posting the code involved with the UserForm?
  • And just to confirm, this code is just under Sheet1?
    Sub Workbook_Open()
    	Range("B7").Select
    	ActiveCell.FormulaR1C1 = InputBox("Enter Cell Phone Bill Cost")
    End Sub

many thanks,

dm
  • 0

#5
Spyderturbo007

Spyderturbo007

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 760 posts

Hello again,

Glad to hear you making some good progress. :)


All thanks to your help.

A couple questions if you don't mind

  • How many sheets in the workbook now?
  • Would you mind posting the code involved with the UserForm?
  • And just to confirm, this code is just under Sheet1?
    Sub Workbook_Open()
    	Range("B7").Select
    	ActiveCell.FormulaR1C1 = InputBox("Enter Cell Phone Bill Cost")
    End Sub


  • There are currently two sheets, one named Investments and one named Bills
  • Code is listed below
  • Correct. Then Sheet2 has the same basic stuff, only different dialog and cells.

Private Sub CommandButton1_Click()
Sheets("Investments").Activate
End Sub

Private Sub CommandButton2_Click()
Sheets("Bills").Activate
End Sub

Private Sub CommandButton3_Click()
Unload Me
End Sub

Thanks again for the help dm!
  • 0

#6
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
Greetings once again,

This is how I currently understand the usage of this workbook.

  • user opens workbook, and a Userform opens that allows the user to select either Investments or the Bills worksheet.
  • Once the user chooses a sheet, an Input box should appear immediately for appropriate data entry.
It sounds like each sheet has its own Input Box, so using your example below. Here is one method to open it from within the UserForm button.

Private Sub CommandButton1_Click()
Sheets("Investments").Activate
Unload Me
End Sub

Private Sub CommandButton2_Click()
Sheets("Bills").Activate
Unload Me
Range("B7").Select
	ActiveCell.FormulaR1C1 = InputBox("Enter Cell Phone Bill Cost")
End Sub

Private Sub CommandButton3_Click()
Unload Me
End Sub


If my assumptions of the usage of the workbook are incorrect, don't hesitate to let me know! :)

Have a great evening.

Sincerely,

dm
  • 0

#7
Spyderturbo007

Spyderturbo007

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 760 posts
You are 100% correct in the usage of the workbook and now that I've moved my input commands to the UserForm as you indicated, it works perfectly. Thank you so much for all your help dm. This will make things so much easier and I don't have to worry about the wrong numbers getting dumped in the wrong cell.

Thanks again, and I appreciate all your help!

Edited by Spyderturbo007, 16 June 2010 - 05:46 AM.

  • 0

#8
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
Hi Spyderturbo007,

Glad I could help out... Hope you have a pleasant week.

:)


Sincerely,

dm
  • 0

#9
Spyderturbo007

Spyderturbo007

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 760 posts
Hi dm,

Well, I'm back with another question, or three. :)

Is there a way to disable the pop-up upon close that asks if you would like to save changes when exiting? I don't want the wife to accidentally save over the file. Basically, I just want to be able to open the document, follow the prompts, print and close leaving the original file in tact.

Every time I try to close the workbook, I get the "Do you want to save the changes to....". Is there a way to turn that off? Also, is there an easy way to automatically prompt the user to print the worksheet after you enter your data? For example, you open the workbook and choose your sheet. You then get the prompts to enter specific data. After adding all the data, can you automatically bring up a print box?

And lastly, how do I check to insure that input boxes are "rational numbers". For example, is there a way to check the user input to make sure the numbers are within a certain range? Maybe 1 - 100, or 1 - 1000?

Thanks again for all your help.

Edited by Spyderturbo007, 18 June 2010 - 06:49 AM.

  • 0

#10
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
Hello again,

I'll have to come back to the first 2 questions, though the first answer would be a yes, and the second is also probable (I'll need to do some further searching).

However, in regards to the below:

And lastly, how do I check to insure that input boxes are "rational numbers". For example, is there a way to check the user input to make sure the numbers are within a certain range? Maybe 1 - 100, or 1 - 1000?


I haven't tested this, but perhaps using Data Validation in the cells you want to restrict the values would be successful?

Data > Data Validation
  • 0

Advertisements


#11
dsenette

dsenette

    Je suis Napoléon!

  • Community Leader
  • 26,047 posts
  • MVP
for the "over saving prevention" make the file read only (right click on the file, choose properties, mark it as read only, apply), if someone ever accidentally presses yes to save it, it will force them to save it as a copy of the original instead of replacing the original file.
  • 0

#12
Spyderturbo007

Spyderturbo007

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 760 posts
The Data > Data Validation doesn't seem to work when you're using the Input Boxes. I tried setting it and then ran through the data entry macro, but it didn't flag any of the values. If I manually enter the data and it's outside the validation range, it works. So it must have something to do with the macro.

I figured out the automatic printing and here is what I used for future reference.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

It works so far and only prints out the sheet selected. I just added it after the last InputBox line and once you enter all the data, it automatically prints out a copy of the sheet.

I found some code online that automatically closed the sheet without prompting for a save, but I couldn't get it to work with the printing. I don't have the code, because after I saved it with the new code, it would kick me out after entering data. There was no way for me to get back into the VB editor to remove the code. :)

I ended up having to go back to my backup. I was thinking about using the "Read Only" option, but if possible, I wanted to get rid of the box all together.

Edited by Spyderturbo007, 18 June 2010 - 01:36 PM.

  • 0

#13
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
Hi again,


While I haven't had time to test with the print code, this code will prevent the "save changes" box from displaying and will not allow any changes to the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Close SaveChanges:=False
End sub

As noted above, this needs to be placed under the "ThisWorkbook" section.

If you find it works fine you can always comment the line out until you need to use its functionality (just add a ' in front of the ActiveWorkbook.... line).
  • 0

#14
Spyderturbo007

Spyderturbo007

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 760 posts
Sorry for getting back to you so late, but my wife had a pile of yard work for me to do and I've been swamped at work lately.

Anyway, your BeforeClose code works great. It seems as though I have to hit the "X" two times, once to close the workbook and once to close Excel, but I can live with that.

I was able to make my own dialog box that prompts the user to choose if they want to print or not, but I'm still struggling with the data validation part. I can't seem to figure that part out. Thanks for all your help so far. I would have never made it this far without you!
  • 0

#15
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
Hello again,

Yard work... I guess now I'm glad we decided to rent a townhouse...no lawns to cut or bushes to trim :)

Though I do miss have my own driveway, etc...but I digress.. back to VBA.

Sorry for the oversight there...to close the workbook and Excel with only one click of an X, revise the BeforeClose event as listed below.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Close SaveChanges:=False
Application.Quit
End sub

I haven't had a chance to review for possible data validation with a Input box, though are you just seeking to keep the values with a certain number range?
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP