Jump to content
Sign in to follow this  
Keltar ™

Excel programming help

Recommended Posts

I made an excel sheet for work for a project we are wanting to present to the higher ups to convince them of its efficacy.

 

Basically it's related to car inspections, and tagging a dollar sign to each inspection.

 

We have the first sheet which is the summary of the day, and the other 16 sheets which represents the 16 vehicles you would check throughout the day. I want to set it so that cell B2 contains the car's model, and said cell becomes the sheet's name once you finish modifying cell B2.

 

I understand I will want to use a Worksheet_Change event, which I have gotten to work, however the issue pops up when I have two cars of the similar make within the same day (aka XLSM file) as it doesn't allow for two sheets to have the same name.

 

So here's my code:

 

 

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$B$2" Then
   Name = [b2].Text
   End If
End Sub
 

 

What happens when I have two cars with the same make, is the following:

 

Runtime error 1004: Cannot rename a sheet to the same name as another sheet, a reference object library, or a workbook referenced by Visual Basic

 

So what do I do to fix this? Would there be a way to add (2) (Incrementally) after the name IF it gives this error popup? I thought of maybe also adding the car's year in the name but that wouldn't be 100% foolproof as it wouldnt be impossible to get two Civic 2008 in the same day.

 

Thanks for your help guys, hopefully I was clear enough in what I explained above.

Share this post


Link to post
Share on other sites

Other little thing, I have created a sheet called Data which holds the value of  each checkboxes.

 

So for example if someone checks the checkbox written Battery, it writes 125$ in a cell.

 

Code is

 

 

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then Range("S1").Value = 125
If CheckBox1.Value = False Then Range("S1").Value = 0
End Sub
 

 

Now this works perfectly for what I want, albeit I'm sure it's not completely efficient. What I would like to do is have the value (in this case 125) be modifiable in the Data sheet. So for example, I'd want it to point to Data!B2 cell. I've tried a few things but I can't seem to get that to work. I tried doing 'Data!B2' but excel seems to give me a syntax error.

Share this post


Link to post
Share on other sites

A decent language would have try/catch structures, but VBA has some shitty error handling thing that I probably wouldn't use. You could implement something to catch that error afterwards, but it's probably easier to check for the specific case prior to assigning the sheet name. Either run through the sheet names to look for duplicates, or keep sheet names in an associative array as they're assigned.

Share this post


Link to post
Share on other sites

I think Excel/Access macros in the workplace tend to be a liability.  What happens if it breaks and you're not around?

Share this post


Link to post
Share on other sites

Well it's not a regular workplace, plus I'd be one of the two users of this file... others would just view it once filled up.

 

I figured a way around it... it's probably not time efficient for a computer but it works. I pull the value from a cell, which is hidden, and put that as the first part of the sheet's name. So sheet one has 1), sheet two has 2), etc.

 

I'm anything but a programmer, so as long as it works and doesn't have too many issues I'd say it's a win!

Share this post


Link to post
Share on other sites

A decent language would have try/catch structures, but VBA has some shitty error handling thing that I probably wouldn't use. You could implement something to catch that error afterwards, but it's probably easier to check for the specific case prior to assigning the sheet name. Either run through the sheet names to look for duplicates, or keep sheet names in an associative array as they're assigned.

 

On Error Resume Next is the bad form, because that will just silently ignore errors.  http://support.microsoft.com/kb/141571 has the 'good' form which is a method call dressed up to look like a goto -- you can use Resume to jump back to where the instruction pointer was before you broke out due to the error.

 

Try/catch is generally terrible and exceptions are generally terrible, but they're here to stay so whatever.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×