UserForm to select Row and Column from 2 prompts

[Bird_FAT]

Hi there all - anybody able to help on this one?!?

I have an appoinment calendar that is one month per worksheet
- Column headings 09:00 - 20:00
- Row headings 'days and dates of the month' (split into two merged cells of two)

***See Image HERE***
​

I have then created a UserForm which has two dropdown combo boxes

***See Image HERE***​

What I want to do is have a way of using the dropdown boxes to select a column and row reference to enter the other data into the four lines in the outlined box area (see Calendar Format.jpg)

I want to be able to select a date - and to have only the dates from the sheet showing would be a bonus! - and have the script use that as its row reference, followed by selecting the time and having it add the column reference, and so selecting the correct cell to be able to then add all the other pieces of data.

I would just set it up so that it all worked by selecting the first cell, then adding, but I'm worried about other users not doing that and so causing things to be misplaced or overwritten!!

Any ideas?
I'm more than willing to change most things, but I do need to have those four pieces of info, and I only need to have a simple calendar.

If anything is not clear, please ask and I'll try to clarify further!

Thanks in advance for any and all help

Forgot to add - In the pics I'm using 2007, but I want to be able to run this on 2000/2003 as that's what's in the offices and it's going to be shared on the server.

Bird

 

[Loucurr]

I had a similar problem and used an input box to find the item I was looking for and then had the sub activate the row and take me to a user form that would apply the info needed to be placed into the spread sheet.

Here is the code I used to find and then select the row.

Sub Find2()
'
' Find2 Macro
' Activates the column to search - searches by column
Range("A1").Activate

'
Cells.Find(What:=InputBox("Security #"), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=True).Activate

' activates the user form to input required text needed to complete the row
UserForm2.Show

End Sub

This code will activate the current row and column you want.
' Range("G" & ActiveCell.Row).Select change "G" accordingly

 

[Bird_FAT]

Cross-Posted

Just got told by another site that I should make sure I post as cross-posted if I do it. Good idea really, lets you guys know what and where, and prevents replicating answers, so fair enough!

Got a good reply on another forum HERE

If you are looking into an issue similar to this, it has good possibilities!

And thanks for the quick reply Loucurr - not sure that it's quite what I'm looking for at the moment, but it has given me an idea for another similar project, so all's good


Bird

 

[Bird_FAT]

The story so far...

Got the following code from jtp on the cross-posted site:

Public Function ReturnStartRange(ByVal tDate As Date, ByVal tTime As String) As Range

    Dim dateRange As Range
    Dim timeRange As Range
    
    'Search for the date in Column A
    Set dateRange = Sheet1.Range("A1:A100").Find(what:=tDate, LookIn:=xlValues)
    If dateRange Is Nothing Then Exit Function
    
    'Search for the time in Row 1
    Set timeRange = Sheet1.Range("A1:Z1").Find(what:=tTime, LookIn:=xlValues, searchorder:=xlByColumns)
    If timeRange Is Nothing Then Exit Function
    
    'Join the two together
    Set ReturnStartRange = Sheet1.Cells(dateRange.Offset(-2, 0).Row, timeRange.Column)

End Function

And now thinking to do the following:

******************
Command button to run series:

Open UserForm1 = Time, Date, Next and Cancel
- Time is a standard Combo Drop Box and puts the time in Sheets(".").Range("B7")
- Date opens UserForm3 = Calendar that takes initial date of active sheet as its month showing
- UserForm3 puts a date in Sheets(".").Range("B6")
- Next opens Macro1 and UserForm2
- Cancel closes UserForm1

'Next' to open Macro1 (jtp's code [above]) then UserForm2 = Client Name, Tel#, Tutor, Reason, New, Close
- Client Name = Text box that enters directly into first cell (offset (0,0))
- Tel # = As above, but in cell (offset (1,0))
- Tutor = Dropdown Combo in cell (offset (2,0))
- Reason as above, but in cell (offset (3,0))
- New = Open UserForm1
- Close = Close

******************

It looks nice and I've got all the forms to run, the New and Close buttons working, the Calendar opening to the active sheet's month, etc; but I'm going to have to fiddle a bit with code to get it all to run smoothly.

What do you all think? To fussy, simpler methods available? Hit me with your thoughts guys!!

Bird

 

[Bird_FAT]

UPDATE - 22 June 2008

The project has changed drastically since it first started and jtp over at excel forum has been giving me a crash course in VBA at a level that is quite far from where I am now.

Having said that, I have learned a hell of a lot in a small space of time, so I'm like a pig in sh!t at the moment - happy as happy can be!

As and when it all gets finished I'll let you know how it turned out, and in what ways it changed! So, if this is a problem you want to see the solution to, then keep a bookmark here and I'll put the code up when it's finished!


Bird