almost there

THX1138

New Member
Joined
Feb 2, 2003
Messages
46
after searching the forum for a solution I have managed to get close to what I need. What I am trying to create is a master sheet that has some vlaues in it. This is then used to generate other sheets with further details for each row item.

Below is the code, what I need is for this code to allow there to be text, numerical data, etc. in the cells to the left (referred in the code as b1,b2, and so on).

Right now the code will not exicute unless the cells are blank.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Target.Column = 2 And Target.Row > 1 And Target.Offset(0, 1) = vbNullString _
And Target <> vbNullString Then
Dim ws As Worksheet
Set ws = Me.Parent.Sheets.Add(After:=Me.Parent.Sheets(Me.Parent.Sheets.Count))
With ws
On Error Resume Next
.Name = Target.Offset(0, -1)
If Err.Number <> 0 Then
MsgBox "Unable to create new worksheet.", vbOKOnly + vbCritical, "Error"
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
Set ws = Nothing
Exit Sub
End If
On Error GoTo 0
.Range("A1:A6") = Application.Transpose(Array("PRIORITY", "PART NUMBER", "DESCRIPTION", "TYPE", "REQUESTED BY", "COMMENTS"))
.Range("B1") = Target.Value
.Range("B2") = Target.Offset(0, 1)
.Range("B3") = Target.Offset(0, 2)
.Range("B4") = Target.Offset(0, 3)
.Range("B5") = Target.Offset(0, 4)
.Range("B6") = Target.Offset(0, 5)
End With
Application.EnableEvents = False
Me.Hyperlinks.Add Target.Offset(0, 6), Address:=vbNullString, SubAddress:="'" & ws.Name & "'!A1", _
TextToDisplay:="PART MASTER SHEET"
Application.EnableEvents = True
Set ws = Nothing
Me.Activate
End If
End If
End Sub

Any and all help will be greatly appreciated.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello THX1138,
Right now the code will not exicute unless the cells are blank.
The only cells that are being tested for blank are (1) the Target cell (must not be blank) and (2) the cell in column C of the target row (must be blank).

Is this not what you want?
As it's written, it's working as intended so far.

(And I'm not sure why you would be making B2 of the new sheet equal to a cell in the original
sheet that must be blank in order for the code to execute, but perhaps that's related to your
request, eh?)
 

THX1138

New Member
Joined
Feb 2, 2003
Messages
46
1. I have adapted the code from other posts for similar issues at this forum. This code was not made for my particular needs. yes, I know just enough to be dangerous. {=P

2. I do not require that any cells be blank, in fact, it would be nice to not have that limitation at all.

This is the root of my question, how do I get rid of the requirement for blank cells? For my purposes, it would be best if it was required that a given number of cells were filled out before it would generate the new sheet. I am unable to find what part of the code determines this.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
It is this second If statement where your limitations are defined:
Code:
If Target.Column = 2 And Target.Row > 1 And Target.Offset(0, 1) = vbNullString _
    And Target <> vbNullString Then
It is stipulating:
If Target.Column = 2 (the changed occurred in column B)
And Target.Row > 1 (change occurred in any row below row 1)
And Target.Offset(0, 1) = vbNullString (cell to the right of the changed cell
- column C in your case - is blank) (This is the one to remove)
And Target <> vbNullString (do not execute if the change was to delete the target cell's value)

To no longer require the cell in column C to be blank you would want to replace that entire
If statement with this:
Code:
If Target.Column = 2 And Target.Row > 1 And Target <> vbNullString Then

Although, requiring the cell to the left of the target (column A) could be tested for not being
blank, and that would allow you to do away with all these lines:
Code:
On Error Resume Next
    .Name = Target.Offset(0, -1)
    If Err.Number <> 0 Then
      MsgBox "Unable to create new worksheet.", vbOKOnly + vbCritical, "Error"
      Application.DisplayAlerts = False
      .Delete
      Application.DisplayAlerts = True
      Set ws = Nothing
      Exit Sub
    End If
    On Error GoTo 0
which is simply testing for the error of creating a new sheet and trying to name it from a cell
that is blank. (But it'll work fine the way it is too.)

Does this help clear anything up?

[EDIT:] By the way, I like the shot of the baby! :LOL:
 

THX1138

New Member
Joined
Feb 2, 2003
Messages
46
Awesome! That was exactlly what I needed. Thank you very much.

"Put the baby down and slowly step away from the donkey . . ."
 

Forum statistics

Threads
1,136,263
Messages
5,674,710
Members
419,521
Latest member
Jasonnie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top