executing code based on current worksheet

[McCondichie]

I have a userform which gathers inputs from the user and then tries to find them in a spreadsheet. Because Excel cannot display all the days in the year because of column limitaions being 256, i put each month on separate sheets within the workbook. Is it possible to have this code find the data in the sheet which is currently selected? If so, how would I go about it???

Many Thanks,

T

 

[xlbo]

There is a larger issue here on workbook design and structure so I will break this into 2 parts

1: To answer your question, you must use the ACTIVESHEET object e.g.

with activesheet
.FIND(theText)
.Do other stuff here
end with

2: As a further recommendation, do not split your data by month in seperate sheets. You say you have done this as you need a day per column...why ? I have never seen a requirement for this that makes sense for data storage. Ideally, your data should be held as in a database table with the date as a field (column) in its own right and any further dimensions e.g. person / client etc as other fields next to the date. This makes any kind of aggregation / consolidation reporting faaaaaaaaaaar easier - and more importantly quicker and easier to implement or change.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[McCondichie]

Thanks for the input xlbo, I have it in a spreadhseet for a few reasons, but specifically because not all users in the company have access (such as my dept) but everyone has Excel. This was dues to our licencing agreement. It would be great to use a database. I'll try the code which you suggested and see how I go. Is it possible to run code based on two sheets at the same time???

Many Thanks

T

 

[stevexff]

I don't think xlbo is suggesting you use an external database. Just put all the data into a single sheet as a table, and select it from there.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[McCondichie]

I don't understand what you mean? Can you put a database into an excel spreadsheet? And if you can, whats the process of doing it.

Thanks for your help,

T

 

[xlbo]

stevexff is correct - I am not suggesting putting your data into a database - I am suggesting having your data, in excel, in a structure which mimics a database table - this is the best and most efficient way to hold data in excel as there are a myriad of functions / tools that will analyse / aggregate data as long as it is in a database format

My issue with the layout of the spreadsheet is because you do not have it in a database format - not that you have it in excel.

My question is why you feel you need a seperate column for each date ? as this is the reason you have quoted for why you want to break the data up into multiple parts

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[McCondichie]

I didn't actually realise that was possible: Is there a post which would detail how to do this or would you be able to explain more?

Many thanks

T

 

[xlbo]

I am not being funny here but you are not answering the questions I am asking of you in order to better understand what you are trying to do and your responses indicate that you do not really understand what I have said so far. All I am suggesting is that you could do with re-arranging your data so it is more condusive to being aggregated / reported on.

Until you answer my question, how can I possibly hope to suggest to you a way of storing your data in a format that you can report on ????


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[McCondichie]

Sorry for my stupidness, kind of lost in translation I guess.

I have stored all dates in a column of their own. the reason for this is because there is an itersection between a date selected through the userform and a value selected from a range in Column A. After they insect, the cell is selected and the color of it is changed. This is possible through VBA code which takes the values of "start date", "End date" and "equipment", with equipment being the range I mentioned as Colum A. the reason it is done this way is for a visual representation to see when the equipment selected is booked out from and to.

I'm not sure how else to organise this data so that the same end result happens. Having trouble trying to show what I mean.

 

[xlbo]

You are not stupid - I just didn't want to go further down the explanation without checking that you were still with me on this !

What I am saying is that you can store the data in a more appropriate way than you are currently doing. The visual representation does not have to be on the same sheet as the data store and can be created at will by using some of the native tools that excel has.

In terms of what this code is doing, I cannot comment but from the sounds of it, all it is doing is using crtieria (2 dates and equipment name) to find an appropriate cell and change the colour of it. This can be done without having to use a different column for each day of the year.

I would suggest that you could hold the data in standard database format and then query it to produce a monthly output that could be generated at will to show what equipment is booked for which days.

What I will also say is that from the sounds of it, you already have a working system - this being the case, it would probably be a reasonable amount of effort to change it. Might be better for if you come to look at this again and to bear in mind for future development - that is why I answered your initial question anyway before talking about more theoretical things like data structures / layouts etc

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[McCondichie]

Thanks for the advice xlbo. I've got a clearer understanding for what you mean now. I'll look more into standard databases in excel before I ask any more questions and also see if its an option to modify my code. I should have kept it in mind when creating the system but I wrote it on Excel 2007 forgeting that Excel XP had limitations in terms of 8bit processing structure.

Many thanks

T