• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Open a new page automatically whenever you enter a new hotel with the same format

Hany ali

Active Member
Hello My Dear , I want Your Help to open New Sheet Automatically When I Enter New Hotel In Column C From Rooming List Sheet asper the Same Format for Aqua Park HRG Sheet ..Provided that the hotel name is in the cell K2 on the new open page ,,,,Thanks Alot
 

Attachments

BobBridges

Active Member
What I think you're asking is this: When the user types a value in col C, check the new value against all the other cells in col C. If it's a new value, one that doesn't exactly match anything else in col C, start a new worksheet.

So first your program needs to be able to determine whether the new value is unique, and second it needs to know how to open a worksheet. Which part is causing you trouble? Or if it's both, we'll talk about one at a time.
 

Hany ali

Active Member
Thanks Alot For Your Reply , Please I Want Both Of Them !
And please, if it was possible for the new page formats to be the same as the Aqua Park HRG Sheet formats because they have important and necessary equations in the file
 

BobBridges

Active Member
Let's start with the first part, then, figuring out whether the new value has already been entered on another row. By the way, how many values are there likely to be in the worksheet? If there are only a dozen or two, you can do it the way I describe below; if there might be thousands, that way would be too slow and we should do it another way that's more work but much faster.

The simplest way, and easiest to understand perhaps, might be to just run down the worksheet looking at every value in col C, like this:
Code:
Set ows = ActiveSheet 'just so we don't have to type "ActiveSheet" over and over again
NewRow = ows.Selection.Row 'assume that the currently selected cell is on the row that was changed
NewHotel = ows.Cells(NewRow,3).Value 'save the value that's in col C
bFound = False 'start out assuming there's no match
For jr = 2 to 30 'for now let's just pretend there are only going to be 30 rows in the worksheet
  If jr = NewRow Then Goto IterateRow 'don't bother looking at the row with the new value
  If ows.Cells(jr, 3).Value <> NewHotel Then Goto IterateRow 'not a match; keep looking
  ' If we got this far then row JR has a match to the new value, so proceed:
  bFound = True 'mark the match
  Exit For 'no need to look further
IterateRow:
  Next jr
If bFound then MsgBox "Match found on row " & jr Else MsgBox "No match found"
This little program has a lot of assumptions in it that would not be justified in the long run. It assumes, for example, that no new hotel value will be entered below row 30. It assumes that after the user types in a new hotel name, he'll make sure the selection is still pointing at the new row before running the program. We can do better than assume those things, but small steps at first. Ask questions about this part, and maybe try it out in Excel. Once you're comfortable with it, we'll add to it.
 

Hany ali

Active Member
if there might be thousands, that way would be too slow and we should do it another way that's more work but much faster.
Indeed there will be thousands of entries for the same hotel
thanks alot for you Helping
 

BobBridges

Active Member
Ok, once we have a slow program working I'll show you how to speed it up. But we'll start with the slow version because it's easier to understand. As I said above, work on adding the above code to your program and ask questions as needed; once you have that working, we'll do the next part.
 

BobBridges

Active Member
Don't forget to test it with a hotel name that is repeated somewhere, to be sure that part of the logic works.

We'll start improving this program soon, tweaking it to make it more reliable. But first, let's finish your main goal, which is to open a new worksheet if no match is found. (That's what you want it to do, right?) We're going to be looking at two worksheets, now, the one with all the hotel names and the new one you're creating, so we'll change the old program so instead of "ows" for the worksheet, we'll use "owsL" (for "list") and "owsN" (for "new"). Like this:
Code:
Set owsL = ActiveSheet
NewRow = owsL.Selection.Row 'assume that the currently selected cell is on the row that was changed
NewHotel = owsL.Cells(NewRow,3).Value 'save the new hotel name in col C
bFound = False 'initialize the match flag
For jr = 2 to 30 'loop through rows 2 to 30
  If jr = NewRow Then Goto IterateRow 'don't bother looking at the row with the new value
  If owsL.Cells(jr, 3).Value <> NewHotel Then Goto IterateRow 'not a match; next!
  ' We found a match:
  bFound = True 'mark the match
  Exit For 'discontinue the loop
IterateRow:
  Next jr

' So we have a match, or not; now what?
If bFound Then
  MsgBox "Match found on row " & jr
Else
  Set owsN = ThisWorkbook.Worksheets.Add 'add a new worksheet
  owsN.Name = NewHotel
  End If
Worksheets.Add creates a new worksheet. I threw in a statement to give a name to the new worksheet, too, arbitrarily naming the new worksheet the same as the new hotel name. Of course, that name may not work for you; maybe it's too long ("The Grand Radisson-by-the-Beach of Tampa"), or maybe it has characters we're not allowed to use in a worksheet name, or something. And you'll probably want to do something more with the new worksheet—type in column headers, maybe. This just gives you the starting idea.

This program should work (unless I made another mistake creating it), but there are some problems with it:
1) After typing in a new hotel name, the user has to move the selection to the new row before running the program. It'd be more convenient, probably, to have the program kick off automatically whenever the user changes a value in column C.
2) As it now stands, this program looks at rows 2 through 30. Better it should figure out for itself how long the list is and look at rows 2 through n. That's not hard.
3) If, as you say, there are hundreds or thousands of rows of hotel names, I think you'll find that this program takes a noticeable length of time looking through all the possible rows. There are several ways to make it go faster. For example, you can load all the thousands or tens of thousands of hotel names into an array in memory and search through them there. This takes more programming, but it runs in a flash so it's often worth the effort.

I'm guessing we'll tackle those in this order: 2, 3, 1. But maybe you're interested in a different order, or don't care about one of those. Anyway, get this new program working and let me know when you're ready to proceed. Ask questions as needed.

Oh, by the way, do you know about hitting <F8>? When you're in the VBA Editor you can hit <F5> to run the program the cursor is pointing at, but you can hit <F8> to go through the program one step at a time, stopping at any given statement to see what the program has done at that point. Very handy for debugging.
 

BobBridges

Active Member
Good. Two questions:
1) Is that result correct? Did you enter a hotel name on some other row (not row 3) and it matched the name on row 3?
2) Does it correctly create a new worksheet when you enter a new hotel name?
 

BobBridges

Active Member
1) Sure. I just wanted to be sure we test that piece of the code.
2) I'm not sure what you mean. Isn't it supposed to create a new worksheet if it sees that the name you entered is a new one? And isn't it doing that?
 

Hany ali

Active Member
2) I'm not sure what you mean. Isn't it supposed to create a new worksheet if it sees that the name you entered is a new one? And isn't it doing that?
Yes that's exactly
 

BobBridges

Active Member
I'm not sure I believe yet. Why did you write "not create a new worksheet when you enter a new hotel name?"? What did you mean?
 

BobBridges

Active Member
Right. It would make sense that there'd be no formatting, though you can teach the program to create headers etc if you want to. We'll worry about that later—or maybe you already know how to do that.

I notice that you're already doing this using a Worksheet_Change routine. I was going to suggest that, but you beat me to it :). But that event will fire when you change any value on the "Rooming List" worksheet, and you want to do this only if a value changes in col 3. So next, instead of assuming that the current selected cell is on the row that you want to check, use the Target variable supplied as an argument to the routine. Like this:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

' First make sure the change was in col 3; otherwise we don't care.
Set oc = Target.Cells(1, 1) 'if more than one cell changed, we'll just look at the top left one
If oc.Column <> 3 Then Exit Sub 'the change wasn't in col 3; exit without comment

' Grab the values we want: worksheet, row, name.
Set owsL = Target.Worksheet 'we'll now get the worksheet from the Target argument
NewRow = oc.Row 'no more assuming the row by looking at the Selection
NewHotel = oc.Value 'save the new hotel name

' Now search for the hotel name elsewhere on the worksheet.
bFound = False 'initialize the match flag
The rest of the program can stay as it was. Try that out—and next, after this works, we'll add something to look not just in the first 30 rows but all the way to the bottom.

By the way, about naming: My personal habit is to name objects using 'o' as the first character. "ows" means a Worksheet to me, "owb" for a Workbook, "org" for a Range, oc for a Cell, like that. You should feel free to name things in a way that makes sense to you; no need to use my naming scheme.
 

herofox

Active Member
you mean now code as following :
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
' First make sure the change was in col 3; otherwise we don't care.
Set oc = Target.Cells(1, 1) 'if more than one cell changed, we'll just look at the top left one
If oc.Column <> 3 Then Exit Sub 'the change wasn't in col 3; exit without comment

' Grab the values we want: worksheet, row, name.
Set owsL = Target.Worksheet 'we'll now get the worksheet from the Target argument
NewRow = oc.Row 'no more assuming the row by looking at the Selection
NewHotel = oc.Value 'save the new hotel name

' Now search for the hotel name elsewhere on the worksheet.
bFound = False 'initialize the match flag
For jr = 2 To 30 'loop through rows 2 to 30
  If jr = NewRow Then GoTo IterateRow 'don't bother looking at the row with the new value
  If owsL.Cells(jr, 3).Value <> NewHotel Then GoTo IterateRow 'not a match; next!
  ' We found a match:
  bFound = True 'mark the match
  Exit For 'discontinue the loop
IterateRow:
  Next jr

' So we have a match, or not; now what?
If bFound Then
  MsgBox "Match found on row " & jr
Else
  Set owsN = ThisWorkbook.Worksheets.Add 'add a new worksheet
  owsN.Name = NewHotel
  End If
End Sub
 

Hany ali

Active Member
now code as following :
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
' First make sure the change was in col 3; otherwise we don't care.
Set oc = Target.Cells(1, 1) 'if more than one cell changed, we'll just look at the top left one
If oc.Column <> 3 Then Exit Sub 'the change wasn't in col 3; exit without comment

' Grab the values we want: worksheet, row, name.
Set owsL = Target.Worksheet 'we'll now get the worksheet from the Target argument
NewRow = oc.Row 'no more assuming the row by looking at the Selection
NewHotel = oc.Value 'save the new hotel name

' Now search for the hotel name elsewhere on the worksheet.
bFound = False 'initialize the match flag
For jr = 2 To 30 'loop through rows 2 to 30
  If jr = NewRow Then GoTo IterateRow 'don't bother looking at the row with the new value
  If owsL.Cells(jr, 3).Value <> NewHotel Then GoTo IterateRow 'not a match; next!
  ' We found a match:
  bFound = True 'mark the match
  Exit For 'discontinue the loop
IterateRow:
  Next jr

' So we have a match, or not; now what?
If bFound Then
  MsgBox "Match found on row " & jr
Else
  Set owsN = ThisWorkbook.Worksheets.Add 'add a new worksheet
  owsN.Name = NewHotel
  End If
End Sub
 
Top