Posts by DerbyNeal

Re: Cell Calculation

Hi thanks,

It works ok but in this example what i need to do is establish if there are ANY combinations that are equal 27 if so, display those first and then output each additional item/items that are less than 27.

so as in my last example:

27 (+ the relevent bin numbers and their associated values)
27 (+ the relevent bin numbers and their associated values)
6 (+ the relevent bin numbers and their associated values)

For course there may be instances where there aren't any combinations equal to 27, therefore they could all be output as separate lines. Or as in the following example, the 2nd and 3rd lines may equal 27 but not the 1st line.

21 Bin1
12 Bin2
15 Bin3

Output as:
27 Bin2 12; Bin3 15;
21 Bin 1 21;

Any more help please?

Thanks again,

Hi,

I am looking for VBA code to solve a current problem.

I have a list of numerical (row) values (Column A) that I am sorting the column (by VBA code) in descending order. Column B is the Bin location. These rows are then output to another worksheet (Column 1 shows the amount and Column 2 displays the Bin location and the amount to be shipped from each Bin). After each output, the original (A & B) columns are re-sorted.

If
Column A = 27
Column B = Bin1
Then

The Output should be
Column 1 = 27
Column 2 = Bin1 27;
written as

Firstly, I need to evaluate each cell value against a specified threshold amount (in this example it is 27)

I have included parts 1 & 2 to provide background. But it is part 3 that I need to solve.

1. Where the cell value is greater than the specified value

If the cell value is greater than the Threshold amount, then I write 27 to the other sheet and replace the cell value with the remainder.

For example:

A B

Firstly,

1 2

is written to the additional worksheet

The column is again sorted in descending order and then the column list becomes:

Then another

is written to the additional worksheet

The column is again sorted in descending order and then the column list becomes:

2. Where the cell value is equal to the specified value

If the Cell value equals the specified amount, then 27 is written to the additional worksheet and the existing row is deleted

For example:

Where

is output to the additional worksheet

The column is again sorted in descending order and then the column list becomes:

3. Where the cell value is less than the specified value

If the 1st Cell value is less than the specified amount (remember they have been sorted), then I need to calculate each row and determine whether multiple values are equal to the specified amount and output the combined amount.

For example:

Where

is written to the output file

The column is again sorted in descending order and then the column list becomes:

And then

Leaving

Which is finally output as

The complete output for section 3 should look like this:

Question

How do I perform section 3 - Where the cell value is less than the specified value?

Thanks in advance,

Hi,

I have created a workbook that restricts specified users from accessing options. This has worked fine for a number of months. However, this week one user (with permissions) has unprotected the workbook/worksheet, made changes to and saved it. When the workbook was re-opened, they found that some features were now unavailable to them. These include the 'Protect' and 'Macro' options from 'Tools'.

I did wonder whether two or more persons were (attempting to) edit the same workbook at the same time, any ideas what else could cause this to happen?

Thanks in advance,

Re: Mouse Functionality

Thanks again, both are great tips.

Re: Mouse Functionality

Thanks!

I'll try it.

Hi,

I have disabled various User functions within a specified Workbook, depending on the User.

A couple of examples are:

'Prevent Right-Click on WorkSheet Tab

Application.CommandBars("PLY").Enabled = False

'Prevent Right-Click on WorkSheet Cells

Application.CommandBars("CELL").Enabled = False

The problem is that one user claims that he is now unable to 'Right Click' on Cells in OTHER Excel workbooks.

Does anyone know how to resolve this? i.e. To limit the restrictions to the specific Workbook?

Thanks in advance,

Hi,

Are the 2 examples the correct methods to prevent right-click on Rows and Columns?

'Prevent Right Click on entire selected Row(s)

Application.CommandBars("Row").Enabled = False

'Prevent Right Click on entire selected Column(s)

Application.CommandBars("Column").Enabled = False

Thanks in advance,

Re: Populating Cells With Specified Dates

Hi,

Excellent....

Again, Many thanks to you!!

Re: Populating Cells With Specified Dates

Cool.

You're a star.

Many, Many, thanks!!

Hi,

I need to populate cells within a WorkSheet cell range with dates from a specified period.

The cells to be populated are in the same column. They must display dates from Monday to Friday ONLY.

For Example:
Mon
Tue
Wed
Thur
Fri
Mon
Tue
Wed
Thu
Fri
Mon
Tue
Wed
Thu
Fri

If the start date required is 1st November, 2006 (a Wednesday), is there a method/facility that I can retrieve the dates to populate these cells within the spreadsheet?

To be displayed as:
Null
Null
01-Nov-06
02-Nov-06
03-Nov-06
06-Nov-06
07-Nov-06
08-Nov-06
09-Nov-06
10-Nov-06
13-Nov-06
14-Nov-06
15-Nov-06
16-Nov-06
17-Nov-06

These dates will continue in the WorkSheet to a specfied date (current or future date).

Thanks in advance,

Re: Sum Based on Matching Criteria

Sorry about that!

I'll try to be more careful in future.

Many thanks,

neal

Hi,

How do I return an offset value within a named range using a formula?

For example, my range "RngTest" is from C3:J43.
If a value of "Product123" is found within the range, (lets say its found
in cells C3; E5 and E7), I need the accumulative values in the cells 'below'
(in cells D3; F5 and F7) added (SUM) and returned to cell L3.

Therefore if Cells D3; F5; and F7 have the respective values of 3; 4 and 5,
the value of cell L3 should be 12.

Also, do I need to express "RngTest" or "C3:J43" or does it matter?

Thanks in advance,

Neal

Re: Tab Properties

Andy,

Thanks mate!

I am fairly familiar with VBA.... But there's always something else to learn.

Neal

Re: Tab Properties

Andy,

I'm sorry, but I don't understand.

Neal

Re: Tab Properties

Hi,

Thanks, but I don't wish to hide the Worksheet Tabs, I just want to prevent the Users from deleting a specific sheet.
I can disable the 'Edit'/'Delete Sheet' Menu Option, however they can still 'right-click' on the tab - i.e. Delete the sheet.

Maybe there is a method within the Menu Options or within VBA to immobilise the 'right-click' options??

Regards,

Neal

Hi,

Excel 2003.

Within my code I have restricted the toolbar options that a user can access (i.e. for Menu Option 'Edit'\'Tools'):

Set myCmd = CommandBars("Worksheet menu bar").Controls("Edit")
myCmd.Controls("Delete Sheet").Enabled = False

But if the user wishes to delete the sheet, they can select the specific WorkSheet 'Tab' and Right-Click to Insert/Delete/Rename the sheet etc.

How do 'hide' these options within VBA? Or is there a Menu setting that I can be set to Enabled = False?

Thanks in advance,

Neal

Hi,

I am trying to create a .csv file in Excel 2003 and need to Output to the file using an Excel cell range (I have to create a .csv file for every Excel WorkSheet in the current WorkBook).
Where strOutFolder = "C:\Test\" And ws.Name is the current WorkSheet.

Dim fs As New FileSystemObject
dim intFileNum as Integer
Dim objNameCSV As Object


.... For Each WorkSheet


intFileNum = FreeFile()


Set fs = CreateObject("Scripting.FileSystemObject")
Set objNameCSV = fs.CreateTextFile(strOutFolder & ws.Name & ".csv", True)


Open strOutFolder & ws.Name & ".csv" For Output As #intFileNum


....Input using the cell range here


Close #nFileNum


.... Next WorkSheet

The following error message appears

Run-time error '70':
Permission denied

Why can't I access the .csv file using the Open statement? Is there an easier way around this?

Thanks in advance.

Neal

Re: 'Hidden' WorkSheet

Thanks,

I'd rather NOT display the worksheet, but it'll do the job!

Regards,

Neal

Hi,

I'm trying to access/read data from a hidden WorkSheet in Excel 2003 using:

Application.Worksheets("Hidden Sheet").Activate
With ActiveSheet


    [B]**** Data ****[/B]


End With

The accessed **** Data **** refers to another ('unhidden') WorkSheet. How can I read the data without making the WorkSheet visible to the User?

Thanks in advance,

Neal

Hi,

Does anyone know where I can find a comprehensive list of '.Cells()' properties that I can Test for/Apply to Excel Cells?

i.e.
Cells(x,y).NumberFormat

I want to set Conditional Formatting using VBA,
to test for:

Data Type (Character, Integer, Date, Decimal, Logical)
Field Length (x(50), 999, 99/99/9999, 999.99, Yes/No)

from an imported file.

Thanks in advance,

Neal