long proccess

[Robert Couchman]

Good morning!

can anyone please help me, i am trying to create a form
that will do the following *i can create the layout but
not the code*

here we go...

BUTTON --> get data + open form

get data - find all rows within the spreadheet that

contain NO DATE OR TIME (column u and v) then put these
into a list to be used by all comboboxes in the form

open form - open the form creating a list for the dates

used in the spreadsheet (column v) (**i already have code
for this stage**)

SELECT DATE --> get times

get times - when a date is selected from the combobox it

will automatically put the times into textboxes in the
correct order of time (**i already have code for this**)

TIME UPDATE --> get people

where the date and time are used in a row on my

spreadsheet i wish to put there record into the combobox
wich is determined by there pannel number (for example. if
they had 3/3/2004 and 14:00 and pannel 3, they would
appear when 3/3/2004 is selected on the same row as
textbox containing 14:00 but in the 3rd combobox accross,
e.g. ComboBox3)

UPDATE COMBOBOX --> edit data

edit data - when a person is selected from the list for

the combobox, it will automatically put the date and time
and pannel number into that persons row (record)

edit data (situation 2) - if a BLANK (or different

person) is selected to overwrite an existing persons time
it will update the existing persons record by putting
BLANKS into the correct areas on there row

CLOSE --> close form

close form - closes form

any help would be greatly appriciated,

Thank you,

Robert Couchman
([email protected])

 

[BrianB]

This will start you off. I doubt that you will get someone to spend th
time to write all your code (grin). You now have the early stages set
Probably because your thinking has been clearer.

There is not enough info afterwards to write code. Incidentally, yo
might like to put each section into its own subroutine so your cod
will look like :-


'=== top of module ==========
Dim statements

Sub Open()
GetData
OpenForm
SelectDate
' etc
End Sub
'=== end of program =========

'=========================================
Sub GetData()
Dim DataSheet As Worksheet
Dim Blanklist As Worksheet
Dim FromRow As Long
Dim ToRow As Long
Dim LookupRange As Range
Dim rg1 As String
Dim rg2 As String
'------------------------
'>get data - find all rows within the spreadheet that
'contain NO DATE OR TIME (column u and v) then put these
'into a list to be used by all comboboxes in the form
Set DataSheet = ThisWorkbook.Worksheets("Sheet1")
Set Blanklist = ThisWorkbook.Worksheets.Add
Blanklist.Name = "Blanklist"
FromRow = 1
ToRow = 1
'--
While DataSheet.Cells(FromRow, 1).Value <> ""
If DataSheet.Cells(FromRow, 21).Value = "" Or DataSheet.Cells(FromRow
22).Value = "" Then
rg1 = "A" & FromRow & ":V" & FromRow
rg2 = "A" & ToRow & ":V" & ToRow
DataSheet.Range(rg1).Copy _
Destination:=Blanklist.Range(rg2)
ToRow = ToRow + 1
End If
FromRow = FromRow + 1
Wend
Set LookupRange = Blanklist.Range("A1:V" & ToRow - 1)
MsgBox (LookupRange.Address)
'----
'==========================================

You do not say what comboboxes you are using. UserForm o
Worksheet/Forms or Control Toolbar. I do not recommend the latte
because they are buggy and too much action can crash workbooks. Bette
to use a userform if the Forms ones are not suitable

 

[Robert Couchman]

Thank you for your code, but really that was all the bits
i could do!

what i am in dire need of is a way of updating my
comboboxes (on a userform) so that it then relates to my
worksheet.

when a record is selected off the list i need the macro to
put the new details into the persons record (either by
having the persons ID number as a listindex or using
lookup to find the person

then the list will update and the person will not appear
on the list in another combobox because they now have a
value for date and time

when selecting a combobox that already contains a persons
details i need the macro to delete the current details

then the lists on all comboboxes will contain that persons
details, as there will be a blank where the date and time
where.

is this even possible??

i have been working on this for a long time and have
slowly come to believe it cannot be done!

thank you,

Robert Couchman
([email protected])

 

[BrianB]

This is code I use to populate a 2 column listbox with data from
worksheet. Comboboxes use the same principle :-

'-------------------------------------------
'- initialise form
Private Sub UserForm_Initialize()
Set DataSheet = ThisWorkbook.Worksheets("data")
Set MyList = DataSheet.Range("FileList")
Rw = 1
FileListBox.Clear
While MyList.Cells(Rw, 1).Value <> ""
FileListBox.AddItem
FileListBox.List(Rw - 1, 0) = MyList.Cells(Rw, 1).Value
FileListBox.List(Rw - 1, 1) = MyList.Cells(Rw, 2).Value
Rw = Rw + 1
Wend
End Sub
'----------------------------------
'------------------------------------------------

Sub ReSetBookCombo()
FindForm.BookCombo.Clear
For Each wb In Workbooks()
FindForm.BookCombo.AddItem (wb.Name)
Next
'FindForm.BookCombo.ListIndex = 0
End Sub

'--------------------------------------------------------