[Kagelude]

Hello all,

It has been a long while since i've coded in VB. I am tring to create standard data entry form for and excel file. Now i have the code to create the list but i do not want Multiple records with the same data. So i would like to create a validation check with in my empty cell loop.

the logic path i'm thinking is

If A1 matches txtNmL (last name) Check If B1 matches txtNmF (first name). IF both are true then message box recored Exsit for this person. Then skip out of the loop and allow editing to that record if needed. Else Contiue search for empty cell to add new record.

This is what i have so far

Private Sub CmdAdd_Click()
    ActiveWorkbook.Sheets("WalkersB").Activate
    Range("A1").Select
    Do
    If IsEmpty(ActiveCell) = False Then     <--------I'd like to do the validation here-----------------
        ActiveCell.Offset(1, 0).Select
    End If
    Loop Until IsEmpty(ActiveCell) = True
    ActiveCell.Value = txtNmL.Value
    ActiveCell.Offset(0, 1) = txtNmF.Value
    ActiveCell.Offset(0, 2) = CboPos.Value
    ActiveCell.Offset(0, 3) = txtStime.Value
    ActiveCell.Offset(0, 4) = txtLtime.Value
    ActiveCell.Offset(0, 5) = txtEtime.Value
    Range("A1").Select
    Call UserForm_Initialize
End Sub

Any help would be much appreciated.

Thanks

This post has been edited by macosxnerd101: 05 November 2010 - 02:46 PM
Reason for edit:: Added code tags.

[Ionut]

Actually you have to search through your sheet while you don't meet empty cell

 
Do While Not IsEmpty(ActiveCell) 
    If ActiveCell.Value = txtNmL.text and ActiveCell.Offset(0,1).Value = txtNmF.text then
        Unload Me 
        Exit Do 
    end if 
    ActiveCell.Offset(1,0).Select
Loop

[Kagelude]

Thanks Ionut, That was a great help... I had to smack myself in the head for not thinking about using a Do While Not condition. I replaced the unload me with a message box instead. But thanks again.

[Kagelude]

Bumb, I have a new issue same app. I am trying to have the using scroll through the form and few the records stored in each row of the excel sheet. The code i have for Pre and Next gets it moving up and down the sheet but i wanted to scroll to back to the bottom of the range once it has reached the first record and visa versa for when the last record is reached.

The problem i am having is because this is a sheet that is not static when it comes to the amount of records its going to hold i am having a hard time getting it to do this. every loop or condition i try seem to be ignored.

This is the code i have for the two command buttons:

Dim R As Integer
Private Sub CmdNext_Click()
    ActiveWorkbook.Sheets("WalkersB").Activate
    Range("A5").Select
    If IsEmpty(ActiveCell) = True Then
        ActiveCell.Range("A5").Select
        End If
    ActiveCell.Offset(R, 0).Select
    txtNmL.Value = ActiveCell.Value
    txtNmF.Value = ActiveCell.Offset(0, 1)
    CboPos.Value = ActiveCell.Offset(0, 2)
    txtStime.Value = ActiveCell.Offset(0, 3)
    txtLtime.Value = ActiveCell.Offset(0, 4)
    txtEtime.Value = ActiveCell.Offset(0, 5)
    txtDays.Value = ActiveCell.Offset(0, 6)
    txtBFID.Value = ActiveCell.Offset(0, 7)
    R = R + 1
End Sub

Private Sub CmdPrev_Click()
 ActiveWorkbook.Sheets("WalkersB").Activate
    Range("A5").Select
    If ActiveCell.Range("A5").Select Then
        Do While Not IsEmpty(ActiveCell)
        ActiveCell.Offset(1, 0).Select
        Loop
    End If
    ActiveCell.Offset(R, 0).Select
    txtNmL.Value = ActiveCell.Value
    txtNmF.Value = ActiveCell.Offset(0, 1)
    CboPos.Value = ActiveCell.Offset(0, 2)
    txtStime.Value = ActiveCell.Offset(0, 3)
    txtLtime.Value = ActiveCell.Offset(0, 4)
    txtEtime.Value = ActiveCell.Offset(0, 5)
    txtDays.Value = ActiveCell.Offset(0, 6)
    txtBFID.Value = ActiveCell.Offset(0, 7)
    R = R - 1
End Sub

I should also mention that i am learning how to code VB all over again as i go along... its been some years since i used it and that was only for the basic "Hello" program in school.

Thanks in advance for the assit and this site has been a great resource for me.

This post has been edited by Kagelude: 12 November 2010 - 10:03 AM

[Ionut]

ActiveCell.Offset(R, 0).Select 

First of all, I don't think this line is correct. That would move with R lines down/up the cursor.
what you need is moving with one row in any direction

ActiveCell.Offset(1, 0).Select 

Secondly, I don't think these lines help you a bit

 Range("A5").Select  
If IsEmpty(ActiveCell) = True Then  
  ActiveCell.Range("A5").Select  
End If  

To make a circular loop through your sheet:

Private Sub CmdNext_Click()  
    ActiveWorkbook.Sheets("WalkersB").Activate  
    if ActiveCell.Value = vbNullString then 
       Cells(1,1).Select 'or whatever is the first record of the table; here is A1, From your example, I suppose it would be something like Cells(5, 1).Select
    end if
    txtNmL.Value = ActiveCell.Value 
    '...    
    ActiveCell.Offset(1, 0).Select
end sub

Private Sub CmdPrev_Click()  
    ActiveWorkbook.Sheets("WalkersB").Activate  
    if ActiveCell.Row = 5 then 
       Cells(GetLastGoodRow,1).Select 
    end if
    txtNmL.Value = ActiveCell.Value 
    '...    
    ActiveCell.Offset(-1, 0).Select
end sub
Private function GetLastGoodRow() as Long
dim iLastRow as Integer
iLastRow = ActiveSheet.UsedRange.Rows.Count

do while ACtiveSheet.Cells(iLastRow, 1).Value = vbNullString 
   iLastRow = iLastRow - 1
Loop

GetLastGoodRow = iLastRow
end funtion

[Kagelude]

Thanks you soo much, I'm going to have to search the web for the VB Library definitions in order to get a better understanding of what tools i have availbale to me, this program is going to be pretty big for what i would like the end result to be.

I can reduce the amount of time I post for help if I understand what functions are in the VB library. Would you happen to know where i can get a good list? It doesn't have to be extremely detailed just a good starting point.

Thanks again

[Ionut]

I think MSDN site is a good place to start

[Kagelude]

Thanks man!! your clutch!!... Much appreciated!! I'll keep this post up to date with my progress (good or bad).

thanks again.

[Ionut]

You're welcome.

Good luck learning! I'll be around if you have other questions.

[Kagelude]

One quick question, although this is not something I will need right away i would to know if it is at all possible. My program at one point will call for excel to pull meeting schedules from Outlook for different individuals, at which point it will place an hour long activity for each individual when they are available.

I want to know is it possible to pull that type of data from outlook at all, or would it be best to just enter those manually (which is what i am trying to avoid)?

The only rule is that only two people can be schedule per any given hour for this activity. Also there are some individuals that are limited to two day of activity or two hours of activity weekly depending on how you want to look at it.


Thanks

[Ionut]

I think it is possible to integrate those two, but I didn't try it yet. Probably MSDN has some good pointers about this.
At a quick thinking, you have to reference some office libraries and from there you will have a access to classes with which you can easily manipulate data.

[Kagelude]

Thanks again for replying, I will continue educating myself and i will post my progress on this tread. I figure i'll keep it going to the end, that way it will be good reference for those having similar issues as me.

[Kagelude]

Ok, i have learned a lot since my last posting. I am stuck at this point.

I will explain what i am trying to do in as much detail as possible.

I have one workbook with 3 spreadsheets each with its own form,

At this moment:
sheet2 and sheet3 forms are complete and entering data as they should.

Bother forms and sheets have a unique field that can be used to link the same record on each sheet (and ID number of sorts).
As you can imagine the reason for this is because data will be needed from each sheet in order to produce and output that is going to show in sheet1.

I have looked at the functions available and am unsure as to which one to use in order to:

1) Frm2 containt on click button to load Frm3
2) On load Frm3 Needs to look at activerow of sheet2 (lets say ActiverCell.Offest(0,7) position) in order to grab the ID for that record pass it on to a declared container "EmpID".
3) now the coding for Sheet3 will using EmpID to perform a search for the matching data containted in EmpID.
4) Once found it should spit out the data found in the activerow or rang onto Frm3 txtfeilds.

I will only be looking at Col A of Sheet3 to find the match of what was passed on from Col H of the Activerow in Sheet2.

Frm3 will also display some data from Sheet2 in two of it txtfeilds.

Private Sub UserForm_Initialize()
    Set Sh_WB = ActiveWorkbook.Sheets("WalkersB")
    Set Sh_WM = ActiveWorkbook.Sheets("WalkersM")
End Sub

Not much i know!!

I just can't wrap my head around how to get this done.

Any ideas will help, i will post if i figure it out before getting a response.

This post has been edited by Kagelude: 16 November 2010 - 12:32 PM

[Kagelude]

Tried to make some edits but i am not able to for some reason. i wanted to all the full code have so far.

Private Sub UserForm_Initialize()
Dim Sh_WB As Worksheet
Dim Sh_WM As Worksheet
    Set Sh_WB = ActiveWorkbook.Sheets("WalkersB")
    Set Sh_WM = ActiveWorkbook.Sheets("WalkersM")
    If Sh_WB.ActiveCell.Offest(0, 7) = Sh_WM.ActivaeCell.Rage("5,0") Then
       txtNmLm.Value = Sh_WB.ActiveCell.Offest
'I figure this is not going to workout, so i stopped here'
End Sub

Still researching options

[Ionut]

i'll think it like this.
Declare a public variable of type integer and on sheet's 2 on_selectchange event, change the value of this variable with ActiveCell.Row.
On Form3 initialization, you have the value of activeRow, so:

Private Sub UserForm_Initialize()  
Dim sValue as String
dim iRowToFillInForm as Integer
Set Sh_WB = ActiveWorkbook.Sheets("WalkersB")  
Set Sh_WM = ActiveWorkbook.Sheets("WalkersM")  
 
sValue = Sh_WM.Cells(ActiveRow, 8).Value 
'don't know what container you use, but I'll close my eyes and use a text box :)/>
txtEmpId.text = sValue

dim rngSheet3 as Range
Set rngSheet3 = Sh_WM.Range("A1:A" & CStr(NumberOfRows)) ' decide what way you calculate the number of rows from column1.
dim rngResponse as Range 
set rngResponse = rngSheet3.Find(sValue)
if not(rngResponse is Nothing) then 
   iRowToFillInForm = rngResponse.Row
   'fill textboxes
end if 

 End Sub