Solved: Select sheet based a combobox value

Jimmy, don't be fooled by the 300+ posts bit for I'd call myself a slow learner (LOL). I've asked that I be reduced in rank status but Jake is hoping I get over the issue, I think.

Actually, now that I know about the debug/compiler bit, it certainly has helped to overcome the silly mistakes. Now the code will definately find the right sheet for deliveries but won't write the data to the correct subsection.

I have used the supplied code that MD gave ( and which works in the Athletic Events (2) example), as the basis for the coding here. I don't understand the setting of the target bit.

[VBA]
Rws = Cells(Rows.Count, 2).End(xlUp).Row
[/VBA]

I'm guessing is meant to find the number of rows in column 2? I don't understand the relevence of this peice of code.

[VBA]
Set Tgt =ActiveSheet.Column(1).Find( What:=Me.cboBlock.Text, Lookat := xlWhole)
[/VBA]

Is setting the target based on the value in the ME.cboBlock.text. I'm guessing that I now need to do a subsequent reset of the target to enable the finding of the last row in requested block number. If I was to write..

[VBA]
Set Tgt= ActiveSheet.Columns(2).Find (After:=Tgt.Offset(0, 1), What:= DTPicker1.Value, Lookat:= xlWhole)
[/VBA]

This I assume now sets the target as the date value in column 2, but does it find the date value in the correct block number?

MD supplied 3 more sets of code as an example, but I need help in understanding what they do as well. I changed the Me.cboEvent.Text to read Me.cboBlock.Text when adapting the code but I suspect the offset bit is now possibly wrong.

[VBA]
'New entry
If Tgt Is Nothing Then
Set Tgt = Cells(Rws, 2).OffSet(2, -1)
Tgt = Me.cboBlock.Text
Next
[/VBA]

The second one is
[VBA]
'Last Entry
If Tgt.End(xlDown).Row = Cells.Row.Count Then
Set Tgt = Cells(Rws, 2).Offset(1, -1)
Else
[/VBA]

and third one is
[VBA]
'other entry
Set Tgt = Tgt.end(xlDown).Offset(-1)
Tgt.Offset(1).Resize(, 6).Insert
End If
End If
[/VBA]

All have different offset values depending on where they are looking from, but only the last one has an insert fuction. So I am sitting here thinking its way too early for a beer.

Are you able to sort that out for me please?

Aussie (Confused) Bear

Okay so far...

Cells(Rows.Count, 2).End(xlUp).Row seems to mean:

"Cells" refers to a range of cells, in this case all the cells in the current worksheet
(Rows.Count, 2) refers to the last cell in column 2 that contains data,
.End(xlUp) refers to a return of a range that represents the cell at the end of the region that contains the source cell ( Note: it requires a direction, in this case xlUp), and
.row refers to???

Mixing it all together (Aussie style) it means the last cell containing data in Column 2, determined by looking from the bottom of the worksheet upwards.

Which sort of defeats the purpose if you have a number of sections on one page to which you would like to write data to.

Ted

Hi Ted :hi:

Quote:

---

Originally Posted by Aussiebear

Cells(Rows.Count, 2).End(xlUp).Row seems to mean:

Mixing it all together (Aussie style) it means the last cell containing data in Column 2, determined by looking from the bottom of the worksheet upwards.

---

That's correct. As far as I know.

I tested your code. Here's what I found.

1) There's a serious error in setting the Target range.
[vba]
'instead of
Set Tgt = ActiveSheet.Column(1).Find(What:=Me.cboBlock.Text, Lookat:=xlWhole)
'use this
Set Tgt = ActiveSheet.Columns(1).Find(What:=Me.cboBlock.Text, Lookat:=xlWhole)[/vba] The only reason I ranked this as a "serious" error is the line On Error Resume Next just before the Set Tgt... line.
You see, Find method goes to error when it doesn't find the thing it's looking for. This is the reason for using On Error Resume Next, as it continues execution on the next line after the error, as if nothing has ever happened.
It was implemented by MD (?) because it's expected that sooner or later the user will select a new block number, so the Find method will go to error, which, in turn, initiates the creation of a new block.

But if it wasn't for this On Error etc. line you would have known that the code is wrong. In it's current state Set Tgt... command always goes to error, not only at new block numbers, so the macro starts a new block every time the Add button is clicked. With that "s" marked red in the code this problem is solved.

2)
[vba] Bales = Me.txtBales.Text
TonsDelivered = Me.txtTonsDelivered.Text
Block = Me.cboBlock.Text[/vba] These lines break the run when the corresponding comboboxes/textboxes are left empty, because of type mismatch error. All 3 variables are declared as Long, while Text property is String.

I can't really suggest a solution here, because I don't understand the purpose of these lines.

3) Finding the proper target row for the next set of data screws up when there's exactly one data row in the chosen block. The algorythm is just plain wrong.

I recommend using the code below. It looks for the block number, first. If Block# isn't found, then creates a new block in the 2nd row after the last used row. If Block# is found, then it looks for the next empty cell in column B, starting in the row of the Block#, downwards.
This algotythm is working correctly as long as column B (Date) and column G (Carrier) is filled

[vba] Rws = Cells(Rows.Count, 7).End(xlUp).Row
Application.ScreenUpdating = False
On Error Resume Next

' Check for Block row
Set Tgt = ActiveSheet.Columns(1).Find(What:=Me.cboBlock.Text, LookAt:=xlWhole)

' New Block
If Tgt Is Nothing Then
Set Tgt = Cells(Rws + 2, 1)
Tgt = Me.cboBlock.Value 'new block number into Column #1
Else

' Existing Block
Set Tgt = ActiveSheet.Columns(2).Find(What:="", After:=Tgt.Offset(, 1), LookAt:=xlWhole).Offset(, -1)
Tgt.Offset(1, 0).EntireRow.Insert
End If
[/vba]

I revamped your code a bit. Corrected the above errors and also implemented a few new things. I think now the code is smaller and more logical. You may not like my style, but if you do, then you're welcome to it :)

Thank you Jimmy.

Quote:

---

Originally Posted by JimmyTheHand

1) There's a serious error in setting the Target range.
[vba]
'instead of
Set Tgt = ActiveSheet.Column(1).Find(What:=Me.cboBlock.Text, Lookat:=xlWhole)
'use this
Set Tgt = ActiveSheet.Columns(1).Find(What:=Me.cboBlock.Text, Lookat:=xlWhole)[/vba] The only reason I ranked this as a "serious" error is the line On Error Resume Next just before the Set Tgt... line.
You see, Find method goes to error when it doesn't find the thing it's looking for. This is the reason for using On Error Resume Next, as it continues execution on the next line after the error, as if nothing has ever happened.
It was implemented by MD (?) because it's expected that sooner or later the user will select a new block number, so the Find method will go to error, which, in turn, initiates the creation of a new block.

But if it wasn't for this On Error etc. line you would have known that the code is wrong. In it's current state Set Tgt... command always goes to error, not only at new block numbers, so the macro starts a new block every time the Add button is clicked. With that "s" marked red in the code this problem is solved.

---

MD kindly offered some code examples for another thread which was posted by myself, trying to build a dummy spreadsheet very similar to this one. I used a number of althletic events on a single page to see if I could find a way to write to subsections on a single page. I then tried to amend the code used in that to this present spreadsheet but in not fully understanding what the various sections of cade was doing, I've obviousily mucked it up. MD's code works very well for what he applied it to.

Quote:

---

2)
[vba] Bales = Me.txtBales.Text
TonsDelivered = Me.txtTonsDelivered.Text
Block = Me.cboBlock.Text[/vba]

These lines break the run when the corresponding comboboxes/textboxes are left empty, because of type mismatch error. All 3 variables are declared as Long, while Text property is String.

I can't really suggest a solution here, because I don't understand the purpose of these lines.

---

With the above issue, I record on a sheet when moisture testing each bale in a load, the number of bales recieved, and later on I print out a weighbridge tonnage weight for the load involved. I need to record these against the Block (stack) to which they were allocated, so an overall count can be maintained, as to the total bales in a block and the total tonnage. Block Numbers are already defined as we preplanned the storage areas. Different blocks may contain either an individual contract, or supplier or straw type, or bale size depending on the location. Some areas will have only a single block whilst others may contain up to six blocks. This is aside from this issue so I will not bore you any further.

As to the issue of the declaration as to Long or String. Am I able to change the .text to a .value ( as in Me.txtBales.Value) or does this create a further issue?

Quote:

---

3) Finding the proper target row for the next set of data screws up when there's exactly one data row in the chosen block. The algorythm is just plain wrong.

I recommend using the code below. It looks for the block number, first. If Block# isn't found, then creates a new block in the 2nd row after the last used row. If Block# is found, then it looks for the next empty cell in column B, starting in the row of the Block#, downwards.
This algotythm is working correctly as long as column B (Date) and column G (Carrier) is filled

[vba] Rws = Cells(Rows.Count, 7).End(xlUp).Row
Application.ScreenUpdating = False
On Error Resume Next

' Check for Block row
Set Tgt = ActiveSheet.Columns(1).Find(What:=Me.cboBlock.Text, LookAt:=xlWhole)

' New Block
If Tgt Is Nothing Then
Set Tgt = Cells(Rws + 2, 1)
Tgt = Me.cboBlock.Value 'new block number into Column #1
Else

' Existing Block
Set Tgt = ActiveSheet.Columns(2).Find(What:="", After:=Tgt.Offset(, 1), LookAt:=xlWhole).Offset(, -1)
Tgt.Offset(1, 0).EntireRow.Insert
End If
[/vba]

---

I have already taken the liberty of predefining (Formatting) the various sheets to include a Block number. I have also created a list on the admin sheet to which the rowsource property of the cboBlock is linked. At this stage I do not envisage any requirement for the code to construct a new block number.

Thank you for your code. I will need to study this to see how your code differs and how this might effect other areas that I had planned to add. If you're on line over the next couple of days I may need to ask you a few questions.
:)

Quote:

---

MD's code works very well for what he applied it to.

---

I wasn't suggesting MD made errors, I referred to him as the one who implemented the On Error etc line. And that line works very well, indeed.

Quote:

---

...or does this create a further issue?

---

I don't think so. However, only testing will reveal the truth. Sometimes you need a number as string, sometimes as numeric. As there are conversion functions in both directions, even if there is a further issue, it can be dealt with.

Quote:

---

If you're on line over the next couple of days I may need to ask you a few questions.

---

I'll be online. Well, most probably.

Good night, Ted :)

Variables should be declared as what the type/intent should be. If they are numeric, they should be defined as Long, Double or Single; if string, they should be defined as String; if date/time, they should be defined as Date, etc. You can always take a numeric and use it as a string later with the Cstr() function, or vice versa with the Clng() function, etc.

I have further amended the code Jimmy kindly supplied (dealing with New Deliveries) for the purpose of the "Tranfers" code. Whilst it is basic at the moment, it correctly transfers from the FromLocation & FromBlock to the ToLocation & To Block as required.

What I'm now chasing is in the Transfers form I need to be able to calculate the Weight of the hay being transferred. When somone enters in the number of bales section on the transfer form, I need the Weight field (txt.Weight.text) to calculate the weight by multiplying the number of bales entered, with the average bale weight value found in column K, two rows below the last row of data, of the Block where the hay is coming from.

Example: If you were to transfer 50 bales from Turkey's Nest Block 3 the value of the weight should automaticily read 18.600. The average bale weight is 372 kgs (Sheet 'Turkey's Nest" K36).

[vba]Private Sub txtBaleCount_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Make sure the From Location and From Block are filled in
If cboFromLocation.ListIndex > -1 And cboFromBlock.ListIndex > -1 Then

Dim ws As Worksheet, frmBlock As Range, x As Range

Set ws = Sheets(Me.cboFromLocation.Text) 'set the right worksheet
With ws
Set frmBlock = .Columns(1).Find(What:=Me.cboFromBlock.Text, LookAt:=xlWhole)
End With

Set x = frmBlock.Offset(, 10).End(xlDown)

Me.txtWeight.Value = x.End(xlDown).Value * txtBaleCount.Value
End If

End Sub[/vba]
Hey Aussie...seems like one the Bale # box is cleared, this might do it. Don't want to mess with the actual file too much.

Edit: I noticed that it is coming up with 18,594.xxx due to the value in K36 being rounded up..?

If you need the rounded value of 372 to be the multiplier then
[vba]
Me.txtWeight.Value = Val(x.End(xlDown).Text) * txtBaleCount.Value
[/vba]
Seemed to work for me. Once I exit the bale# text box the value appears automatically.

[quote=XLGibbs][vba]Private Sub txtBaleCount_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Make sure the From Location and From Block are filled in
If cboFromLocation.ListIndex > -1 And cboFromBlock.ListIndex > -1 Then

Dim ws As Worksheet, frmBlock As Range, x As Range

Set ws = Sheets(Me.cboFromLocation.Text) 'set the right worksheet
With ws
Set frmBlock = .Columns(1).Find(What:=Me.cboFromBlock.Text, LookAt:=xlWhole)
End With

Set x = frmBlock.Offset(, 10).End(xlDown)

Me.txtWeight.Value = x.End(xlDown).Value * txtBaleCount.Value
End If

End Sub[/vba]

Just seeking confirmation here regarding the use of the frmBlock bit... is that a misspelling or not?

Did you mean fromBlock or have you created a form somewhere that you refer to as frmBlock?

Ted

[quote=Aussiebear]

Quote:

---

Originally Posted by XLGibbs

[vba]Private Sub txtBaleCount_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Make sure the From Location and From Block are filled in
If cboFromLocation.ListIndex > -1 And cboFromBlock.ListIndex > -1 Then

Dim ws As Worksheet, frmBlock As Range, x As Range

Set ws = Sheets(Me.cboFromLocation.Text) 'set the right worksheet
With ws
Set frmBlock = .Columns(1).Find(What:=Me.cboFromBlock.Text, LookAt:=xlWhole)
End With

Set x = frmBlock.Offset(, 10).End(xlDown)

Me.txtWeight.Value = x.End(xlDown).Value * txtBaleCount.Value
End If

End Sub[/vba]

Just seeking confirmation here regarding the use of the frmBlock bit... is that a misspelling or not?

Did you mean fromBlock or have you created a form somewhere that you refer to as frmBlock?

Ted

---

It is a range declared in the code I wrote (Now Bolded above). Just paste that event code into the Transfer Form code...you can rename that variable if you like...

If I use you initial code it works as you say. The weight comes up with the extended value, however if I alter the code so as to use the 372 as the multiplier it halts on that line telling me I have a sub or function not defined. The word Value after the equals sign is highlighted.

Do we need to declare this as Dim Value As Long?

Ted

Me.txtWeight.Value = Val(x.End(xlDown).Text) * txtBaleCount.Value


As per the PM , change Value to Val. I am combining languages again.

The idea is that x.end(xlDown).text gives us the displayed value (in this case 372). We want that as a value for multiplying. VBA uses VAL() to do that.

Thanks XL. Works fine. Now I have 3 months of transfers to fit into the full workbook.

In fact thanks to all who have contributed to this issue. Without your efforts I'd still be sitting here in the dark.

Ted

If it is Dark, you should try this with your monitor turned on. It helps:cleverman

The monitor..... Now why didn't I think of that? :banghead::banghead:

Hi Ted,

Why are you doing this in Excel? It looks like a database, and you might be better off with Access. We could help you with that, too.

If you stay with Excel, I'd have a few recommendations to refine the project. Would you like to hear the details?

Jimmy, our guys at work (local site)are Excel fixated. If it can't be glued and screwed to a spreadsheet, their not interested. Whereas Head Office want everything written in Progress, so to maintain a little bit of independence its Excel or bust.

I have no doubt it could be more easily done in Access but mere slaves do not argue well with management, if you want a long term employment.

Ted

Ted,

One of the best ways to convince anyone (including management) is to knock up a prototype, with enough functionality to show how it works and how it is easy. You could do thiat in Access, with the Access db and use ADO to pull the data into Excel for reporting, or even an alternative Excel solution (Excel db workbook and an Excel logic workbook). The trick is always to know your business and the business model, and build a solution that you know will be useful, rather than waiting for the drive of the management (who are often naturally divorced from the day to day realities of the data, the systems, etc.).

However, and I do not mean this in a derogatory manner in any way shape or form, from your questions over the past year you seem to have enough on your plate doing your day job as well as expanding your Excel skills to meet the system demands imposed on you. If I were in your position, I would concentrate on Excel, it may not always be the best solution, but it is definitely the best all-round one-stop solution.

And hopefully I am just in time to wish you a happy new year. Don't forget to raise that glass.

XLD, I got that many people n the list, I've had to start early.... but I've got 51 mins to go, so I'll toast you a second time.

I had asked much earlier in another thread re the possibility about parent child workbook relationship, but the focus was on security more than anything else.

I'm happy to go with Excel. The company sent 3 of us off to do an Access course ( 2 days) middle of 2006, but it went nowhere really.

Ask the parent child workbook question again, it could make a nice 2007 topic.

Must be nearly midnight there as I type this.