PDA

View Full Version : Solved: Select sheet based a combobox value



Aussiebear
12-26-2006, 04:46 AM
I currently have a workbook consisting of sheets named after a series of storage locations. I am also using a form with a combobox to select a location, which I then hope to use as the value to select a worksheet.

Can I write something like this?

If Trim (Me.cboLocation.text) = "" Then
Me.cboLocation.setFocus
MsgBox "Please enter a Location"
Exit Sub
Else

If Trim(Me.cboLocation.text) = "Commodity Shed" Then
Sheet ("Commodity Shed").Select
Sheet("Commodity Shed").Activate
Else
If Trim (Me.cboLocation.text) = "Turkey's Nest" Then
Sheet ("Turkey's Nest").Select
Sheet("Turkey's Nest").Activate
Else
If Trim(Me.cboLocation.text) = "West Q Alley" Then
Sheet ("West Q Alley").Select
Sheet ("West Q Alley").Activate
End If
End If
End If
End If


Will firstly select the sheet as indicated in the combo box and in turn will it then make it the active sheet?

Ted

JimmyTheHand
12-26-2006, 05:26 AM
In my experience there's no difference between Sheet.Activate and Sheet.Select. Any one of them is sufficient.

I guess (hope?) you use the rowsource and controlsource properties of the combobox. If so, then you might find the following code useful:

Private Sub CommandButton1_Click()
On Error GoTo NoMatch
If Application.WorksheetFunction.Match(Range(ComboBox1.ControlSource), Range(ComboBox1.RowSource), 0) > 0 Then
Sheets(Range(ComboBox1.ControlSource).Value).Activate
Exit Sub
End If
NoMatch:
MsgBox ("Sheet doesn't exist")

End Sub
EDIT:
A little explanation is due.
The code above uses the worksheetfunction called "MATCH" to look up the current text of ComboBox1 in it's rowsource. In other words, it examines whether the text in the combobox is chosen from the list or entered manually. If it's entered manually, then MATCH goes to error, so the message "Sheet doesn't exist" appears. If it's from the list, then the chosen sheet will be activated.

Aussiebear
12-26-2006, 05:37 AM
Yes use rowsource to dicate the choice of locations available, but I need to know from what I have posted above, will it select the correct sheet as indicated by the value selected in the combobox?

Once I get past this point, I will be trying to build up a write to a section on the selected sheet.

Am I on the right track if I simply remove the line/s which are

[vba]
Sheet ("SheetName").Activate
[\vba]

I don't need another command button

JimmyTheHand
12-26-2006, 05:52 AM
Yes, you are on the right track. :thumb
But that's quite easy to check, you just have to test your own code.

I didn't recommend another command button. Merely showed a 4-line code to check whether the text of the combobox is from the list or not. If you will have a dozen sheets, or change sheet names in the future, perhaps you will see the point. :whistle:

Zack Barresse
12-26-2006, 09:17 AM
There is a very big difference between Select and Activate actually. ;)

When you Select something, you can do a multi-select. If another sheet is activated, you can select another one. When you activate a sheet, you disallow multi-select. This can be advantageous when dealing with multiple sheets at once.

Take this code for example...

Sub SelectSheets()
Dim i As Long, TopPos As Long, SheetCount As Long
Dim PrintDlg As DialogSheet, CurrentSheet As Worksheet
Dim cb As CheckBox, arrSheets() As String, wsCurr As Object
Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
SheetCount = 0
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
If Application.CountA(CurrentSheet.Cells) <> 0 And CurrentSheet.Visible Then
SheetCount = SheetCount + 1
ReDim arrSheets(1 To SheetCount)
arrSheets(SheetCount) = CurrentSheet.Name
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i
TopPos = TopPos + 13
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount + 1).Text = "Print All"
PrintDlg.Buttons.Left = 240
With PrintDlg.DialogFrame
.Height = Application.WorksheetFunction.Max(68, PrintDlg.DialogFrame.Top + TopPos - 15)
.Width = 230
.Caption = "Select sheets to print"
End With
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
If cb.Text = "Print All" Then
Set wsCurr = ActiveSheet
Sheets(arrSheets).Select
ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False
wsCurr.Activate
Exit For
End If
Worksheets(cb.Caption).Activate
ActiveSheet.PrintOut
End If
Next cb
End If
Else
MsgBox "All worksheets are empty."
End If
Application.DisplayAlerts = False
PrintDlg.Delete
Sheets("Corp").Select
End Sub

You see a big difference between Select and Activate here.

HTH

JimmyTheHand
12-26-2006, 09:57 AM
Hi Zack :hi:

I see your point. :bow:
So what do you suggest, which method should be used in Aussiebear's particular case? Select or Activate?

I studied your code, interesting. I've almost forgot about good old DialogSheets. I've not used such things since... :think: Office 97. And built-in help is not very enthusiastic in propagating them, either... Took me some time to grasp it, but one question remains. Could you please tell me why these lines are needed?
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront
I see no difference between when they are executed and when they are not.

I've also found one error. Maybe an older version of Excel takes it well, but in V.2003 code execution breaks on
Sheets(arrSheets).Select because
ReDim arrSheets(1 To SheetCount) clears all previously set values from the array. You may want to update this in your set of code examples. ;)

Jimmy

Zack Barresse
12-26-2006, 02:15 PM
Ah, that should be Redim Preserve, thanks for the catch. :)

As for AB case, I would use Activate. I always use Activate unless I am multi-selecting a sheet. I would even think about disabling events while I'm doing it, as that will fire the SheetActivate event. Depending on the workbook structure, this could be a good thing or a bad one. Either way, it is a variable worth considering.

As far as the buttons go, I honestly don't remember why they were there. This code originated from Aaron Blood, I just added the button to print all, as it was a request from the MrExcel board on a recent thread (which is why I used it for an example).

Aussiebear
12-26-2006, 02:38 PM
Thanks Jimmy. I will be using a predefined known number of sheets which are named as a range and called in rowsource properties of the combo box.

Thanks Zack. I didn't follow your code but I will use the Sheet("Sheet Name").Activate as suggested.

Ted

Zack Barresse
12-26-2006, 03:08 PM
AB, here is a commented piece of code...

Sub SelectSheets()

'Dimension all variables
Dim i As Long, TopPos As Long, SheetCount As Long
Dim PrintDlg As DialogSheet, CurrentSheet As Worksheet
Dim cb As CheckBox, arrSheets() As String, wsCurr As Object

'Turn off screen flickering
Application.ScreenUpdating = False

'Check if workbook is protected, exit if so
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

'Set variables
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
SheetCount = 0
TopPos = 40

'Loop through all worksheets
For i = 1 To ActiveWorkbook.Worksheets.Count

'Reset the varaible 'CurrentSheet'
Set CurrentSheet = ActiveWorkbook.Worksheets(i)

'Check if there is any data on the worksheet and if it is visible
If Application.CountA(CurrentSheet.Cells) <> 0 And CurrentSheet.Visible Then

'Add one to the variable if we know it is a good sheet
SheetCount = SheetCount + 1

'Add one to the array, preserving all past values
ReDim Preserve arrSheets(1 To SheetCount)

'Set the current iteration (of the array) value
arrSheets(SheetCount) = CurrentSheet.Name

'Add the checkbox and name it the name of the valid worksheet
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = CurrentSheet.Name

'Adjust the top position of the form, keeping uniform spacing
TopPos = TopPos + 13

End If

Next i

'Add a little more for the top position, separating the Print All button from the rest
TopPos = TopPos + 13

'Add the Print All button
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount + 1).Text = "Print All"
PrintDlg.Buttons.Left = 240

'Set the area for the dialog sheet/form
With PrintDlg.DialogFrame
.Height = Application.WorksheetFunction.Max(68, PrintDlg.DialogFrame.Top + TopPos - 15)
.Width = 230
.Caption = "Select sheets to print"
End With

'Bring buttons to the front (unkown if needed???)
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront
CurrentSheet.Activate

'Turn screen updating back on
Application.ScreenUpdating = True

'Check if there were any valid sheets
If SheetCount <> 0 Then

'If the dialog sheet was created..
If PrintDlg.Show Then

'Loop through each checkbox
For Each cb In PrintDlg.CheckBoxes

'Test if the checkbox value was checked or not
If cb.Value = xlOn Then

'Check if the checkbox value was the Print All button
If cb.Text = "Print All" Then

'Set sheet to return to
Set wsCurr = ActiveSheet

'Select all sheets in the array which met the conditions
Sheets(arrSheets).Select

'Print out selected sheets
ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False

'Re-activate the last active sheet
wsCurr.Activate

'Exit sheet at end of Print All routine
Exit For

End If

'If not the Print All button, activate the sheet and print it
Worksheets(cb.Caption).Activate
ActiveSheet.PrintOut

End If

Next cb

End If

Else

'If no sheets were counted, give a message box
MsgBox "All worksheets are empty."

End If

'Turn off events to delete worksheet, else an alert will display
Application.DisplayAlerts = False
PrintDlg.Delete

'Turn alerts back on
'THIS IS A NEW ADDITION, NOT SPOTTED BEFORE
Application.DisplayAlerts = True

'Select the first sheet again ("Corp" was specific to the last users solution)
Sheets("Corp").Select

End Sub

HTH

JimmyTheHand
12-26-2006, 04:29 PM
I suggest one comment to change.

'If the dialog sheet was created..
If PrintDlg.Show Then Actually, a dialog sheet, by default, is created with two buttons: OK and Cancel
PrintDlg.Show here behaves as a function, which returns true when OK button was clicked, and returns false when Cancel was clicked. So the comment should be:

'If user clicked on OK button on the dialog sheet...
If PrintDlg.Show Then

Aussiebear
12-26-2006, 05:33 PM
Thanks Zack

Aussiebear
12-27-2006, 03:57 AM
Okay have run into a couple of issues here. Sheet selection method is not working. To get there, enable macros/ click on deliveries and view the form with a multicombo & text data entry. This is what I'm trying to enter via the form.

The code fails on the very first sheet selection effort. What I was hoping for is that I can use the value of the cboLocation choice as the method of selecting the correct sheet and then using code very similar to that which was suggested in the other thread "writing data to subsections on the same page".

Seems I've bitten off more than I can chew, but being a fearless potential athlete, I stepped out into the darkness. Can someone please turn the light on??

Ted

Aussiebear
12-27-2006, 03:58 AM
File attached for above post

JimmyTheHand
12-27-2006, 04:02 AM
Could you upload the workbook? Or, at least, post the code that fails, with the line, where it fails, marked.

EDIT:
Ah, it's uploaded now. I didn't see it, sorry.

JimmyTheHand
12-27-2006, 04:31 AM
Well... There are a couple of errors in your code.

On code module of userform "frmTransfer"

Variables FromLocation, FromBlock, ToLocation and ToBlock were not defined

Replace FromBlock = MecboFromBlock.Text
with FromBlock = Me.cboFromBlock.Text

On code module of userform "frmDelivery"

put an "s" into Sheet("Commodity Shed").Activate
like this: Sheets("Commodity Shed").Activate
and repeat this with all similar commands.
(Actually, I think this was the root of the problem you had.)

replace Me.txtBales , Value = ""
with Me.txtBales.Value = ""

On code module of sheet "Silage"

The sub below refers to a userform called "frmAudit", whereas no such thing exists:

Private Sub Audit_Click()
frmAudit.Show
End Sub replace Workbook.Close
with ThisWorkbook.Close

Let's see what happens.
:)
Jimmy

Aussiebear
12-27-2006, 04:40 AM
Jimmy, Haven't got as far as the audit bit yet. Just wanted to get the sheet selection and the write data to the correct subsection (stack) on the run first then I need to somehow work out how to transfer hay from one section to another, then I was thinking of somehow creating a pivotsheet of the various storage areas (including stacks) so that as hay becomes used over the next twelve months, we can have a handy end of month audit method.

Aussiebear
12-27-2006, 06:49 AM
This one's for you Bob or Jimmy, if you're game! I've done as Jimmy has suggested plus a bit more. Deliveries and transfers are mostly done I think. Can you check for errors please?

Be advised errors range from "didn't have a clue" to "the effects of the last six beers.", but hey I had a go.

Ted

JimmyTheHand
12-27-2006, 07:52 AM
Ted :think:

You can do the same thing that I would do. Use the compiler to check for errors. In VB Editor select Debug / Compile VBAProject. It will comb through all modules looking for errors, and stop if it finds one. I'm sure you can correct those errors (there are a few).

I found one thing that wasn't picked up by compiler, because it's not code error. Look at this part of the code, focusing on the red lines:
FromLocation = Me.cboFromLocation.Text
FromBlock = Me.cboFromBlock.Text
ToLocation = Me.cboToLocation.Text
ToBlock = Me.cboFromBlock.Text
BaleCount = Me.txtBaleCount.Text

'Check for From Location
If Trim(Me.cboFromLocation.Text) = "" Then
Me.cboFromLocation.SetFocus
MsgBox "Please enter a From Location"
Exit Sub
The point is that first you create a variable to hold the value of Me.cboFromLocation.Text, then... don't use it?

Jimmy

PS:
Six beers are six beers, I understand :yes

Aussiebear
12-27-2006, 01:13 PM
I thought this was necessary to check for an error just in case the transfer button had been selected without any data being in the cboFromLocation.

Jimmy, I have almost zero knowledge with vba, so there's lots to learn. Let me crawl before you ask me to run.

Ted

JimmyTheHand
12-27-2006, 01:41 PM
I thought this was necessary to check for an error just in case the transfer button had been selected without any data being in the cboFromLocation.
I'm a bit confused on this one. It could be my lack of command of English. I mean, I understand the need to check for errors, but could you do the checking after all, with the compiler? Or need detailed help with it?



Jimmy, I have almost zero knowledge with vba, so there's lots to learn. Let me crawl before you ask me to run.
I'm sorry, I'm sort of newbie here, and think of almost everyone as my better/senior. Your 330+ posts and status as VBAX Mentor (!) look quite impressive to me. I scarcely believe what you just said.

So what next? :saywhat:
I can debug the code if that is your wish.

Jimmy

Aussiebear
12-27-2006, 04:34 PM
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.


Rws = Cells(Rows.Count, 2).End(xlUp).Row


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.


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


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..


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


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.


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


The second one is

'Last Entry
If Tgt.End(xlDown).Row = Cells.Row.Count Then
Set Tgt = Cells(Rws, 2).Offset(1, -1)
Else


and third one is

'other entry
Set Tgt = Tgt.end(xlDown).Offset(-1)
Tgt.Offset(1).Resize(, 6).Insert
End If
End If


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

Aussiebear
12-27-2006, 08:43 PM
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

JimmyTheHand
12-28-2006, 02:43 AM
Hi Ted :hi:


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.

'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) 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)
Bales = Me.txtBales.Text
TonsDelivered = Me.txtTonsDelivered.Text
Block = Me.cboBlock.Text 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

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


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 :)

Aussiebear
12-28-2006, 04:11 AM
Thank you Jimmy.



1) There's a serious error in setting the Target range.

'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) 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.



2)
Bales = Me.txtBales.Text
TonsDelivered = Me.txtTonsDelivered.Text
Block = Me.cboBlock.Text

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?



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

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



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.
:)

JimmyTheHand
12-28-2006, 04:36 AM
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.


...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.


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 :)

Zack Barresse
12-28-2006, 09:26 AM
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.

Aussiebear
12-30-2006, 05:01 PM
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).

XLGibbs
12-30-2006, 05:26 PM
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
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

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

Seemed to work for me. Once I exit the bale# text box the value appears automatically.

Aussiebear
12-30-2006, 06:55 PM
[quote=XLGibbs]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

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

XLGibbs
12-30-2006, 06:56 PM
[quote=XLGibbs]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

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...

Aussiebear
12-30-2006, 07:26 PM
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

XLGibbs
12-30-2006, 07:39 PM
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.

Aussiebear
12-30-2006, 08:37 PM
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

XLGibbs
12-30-2006, 08:46 PM
If it is Dark, you should try this with your monitor turned on. It helps:cleverman

Aussiebear
12-30-2006, 11:22 PM
The monitor..... Now why didn't I think of that? :banghead::banghead:

JimmyTheHand
12-30-2006, 11:36 PM
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?

Aussiebear
12-31-2006, 01:06 AM
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

Bob Phillips
12-31-2006, 04:24 AM
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.

Aussiebear
12-31-2006, 06:13 AM
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.

Bob Phillips
12-31-2006, 06:51 AM
Ask the parent child workbook question again, it could make a nice 2007 topic.

Must be nearly midnight there as I type this.

Aussiebear
12-31-2006, 07:00 AM
Yep. As of Now it's Happy New Year

JimmyTheHand
01-03-2007, 02:55 PM
As I promised, here's the improved version.
I tried to be thorough while testing, but it't getting late here.... I might have missed something.

Jimmy

mail2bharath
01-04-2007, 05:59 AM
thanq

Aussiebear
01-05-2007, 01:03 AM
Not bad Jimmy.