View Records

Sort by date Sort by votes
sijpie said:
Don't understand why it was the old version (with later date) but anyway here is the correct one. Renamed it just to make ure...

https://docs.google.com/file/d/0BxykuY6uKAJ9R0FVZnpJMnI2RTA/edit?usp=sharing
Click to expand...


thank you.. i already check the file what i mean is there is no changes regarding the loading of records from logsheet to form.. the check box or option button will not selected even if that checkbox has record in the logsheet.. also the code is the same to the one i attach.. thank you again for fast reply..
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Is the file called IR-new.xlsb ?

It certainly works on my computer, and the code is totally different.

Code: 
'------------------------------------------
Private Sub cmdOpen_Click()
' OK button
' Fill out the selected details in the Form
'------------------------------------------
    Dim wsFrm As Worksheet
    Dim wsLog As Worksheet
    Dim rFnd As Range, rOut As Range
    Dim vArr As Variant
    
    Set wsLog = Sheets(Me.SheetNm.Caption)
    Set wsFrm = Sheets("IRForm")
    
    ClearData wsFrm
    
    ReDim vArr(1 To 1, 1 To 5) ' columns B:G
    
    Set rFnd = wsLog.Cells(Me.StartRow, "B")
    Set rOut = wsFrm.Cells(22, "B")
    
    With rFnd
        wsFrm.Range("G3") = .Offset(0, 0).Value
        wsFrm.Range("G4") = .Offset(0, 1).Value
        wsFrm.Range("G7") = .Offset(0, 2).Value
        wsFrm.Range("C15") = .Offset(0, 3).Value
        wsFrm.Range("C16") = .Offset(0, 4).Value
        wsFrm.Range("E16") = .Offset(0, 5).Value
        wsFrm.Range("B18") = .Offset(0, 6).Value
        wsFrm.Range("C18") = .Offset(0, 7).Value
        wsFrm.Range("D18") = .Offset(0, 8).Value
        wsFrm.Range("E18") = .Offset(0, 9).Value
        wsFrm.Range("F18") = .Offset(0, 10).Value
        wsFrm.Range("C30") = .Offset(0, 34).Value
        wsFrm.Range("C31") = .Offset(0, 35).Value
        wsFrm.Range("C33") = .Offset(0, 36).Value
        
        
    End With
        
    Do While rFnd.Value = cmbSubNo.Value And rFnd.Offset(0, 1).Value = cmbRevNo.Value
        With rFnd
            vArr(1, 1) = .Offset(0, 4).Value
            vArr(1, 3) = .Offset(0, 5).Value
            vArr(1, 4) = .Offset(0, 6).Value
            vArr(1, 5) = .Offset(0, 7).Value
            'vArr(1, 6) = .Offset(0, 8).Value
            
            'rOut.Resize(1, 2).Value = vArr
            Set rOut = rOut.Offset(1, 0)
            Set rFnd = .Offset(1, 0)
        End With
    Loop
    ' check if earlier revision, if so lock sheet
    If cmbRevNo.ListIndex <> cmbRevNo.ListCount - 1 Then
        ProtectForm wsFrm, True
    Else
        UnProtectForm wsFrm, False
    End If
    Unload Me
    
End Sub
 
Upvote 0
yes i downloaded IR-new.xlsb file.. my excel version is 2007

but this is the code from my old file:

Code: 
Private Sub cmdOpen_Click()
' OK button
' Fill out the selected details in the Form
'------------------------------------------
    Dim wsFrm As Worksheet
    Dim wsLog As Worksheet
    Dim rFnd As Range, rOut As Range
    Dim vArr As Variant
    
    Set wsLog = Sheets(Me.SheetNm.Caption)
    Set wsFrm = Sheets("IRForm")
    
    ClearData wsFrm
    
    ReDim vArr(1 To 1, 1 To 5) ' columns B:G
    
    Set rFnd = wsLog.Cells(Me.StartRow, "B")
    Set rOut = wsFrm.Cells(22, "B")
    
    With rFnd
        wsFrm.Range("G3") = .Offset(0, 0).Value
        wsFrm.Range("G4") = .Offset(0, 1).Value
        wsFrm.Range("G7") = .Offset(0, 2).Value
        wsFrm.Range("C15") = .Offset(0, 3).Value
        wsFrm.Range("C16") = .Offset(0, 4).Value
        wsFrm.Range("E16") = .Offset(0, 5).Value
        wsFrm.Range("B18") = .Offset(0, 6).Value
        wsFrm.Range("C18") = .Offset(0, 7).Value
        wsFrm.Range("D18") = .Offset(0, 8).Value
        wsFrm.Range("E18") = .Offset(0, 9).Value
        wsFrm.Range("F18") = .Offset(0, 10).Value
        wsFrm.Range("C30") = .Offset(0, 34).Value
        wsFrm.Range("C31") = .Offset(0, 35).Value
        wsFrm.Range("C33") = .Offset(0, 36).Value
        
        
    End With
        
    Do While rFnd.Value = cmbSubNo.Value And rFnd.Offset(0, 1).Value = cmbRevNo.Value
        With rFnd
            vArr(1, 1) = .Offset(0, 4).Value
            vArr(1, 3) = .Offset(0, 5).Value
            vArr(1, 4) = .Offset(0, 6).Value
            vArr(1, 5) = .Offset(0, 7).Value
            'vArr(1, 6) = .Offset(0, 8).Value
            
            'rOut.Resize(1, 2).Value = vArr
            Set rOut = rOut.Offset(1, 0)
            Set rFnd = .Offset(1, 0)
        End With
    Loop
    ' check if earlier revision, if so lock sheet
    If cmbRevNo.ListIndex <> cmbRevNo.ListCount - 1 Then
        ProtectForm wsFrm, True
    Else
        UnProtectForm wsFrm, False
    End If
    Unload Me
    
End Sub

the problem is when updating the records still the checkbox or option button is not selected..
 
Upvote 0
Try it again, it should. I can't figure why it works here and not with you.

Alternatively you can recoded it to put the values in the (to be hidden) linked cells, that will do the same thing.
 
Upvote 0
thank you i already solved the problem.. i try the alternative that linked to the cells.. thank you again..
Code: 
Private Sub cmdOpen_Click()
' OK button
' Fill out the selected details in the Form
'------------------------------------------
    Dim wsFrm As Worksheet
    Dim wsLog As Worksheet
    Dim rFnd As Range, rOut As Range
    Dim vArr As Variant
    
    Set wsLog = Sheets(Me.SheetNm.Caption)
    Set wsFrm = Sheets("IRForm")
    
    ClearData wsFrm
       
    Set rFnd = wsLog.Cells(Me.StartRow, "B")
    Set rOut = wsFrm.Cells(22, "B")
    
    With rFnd
        wsFrm.Range("G3") = .Offset(0, 0).Value
        wsFrm.Range("G4") = .Offset(0, 1).Value
        wsFrm.Range("G7") = .Offset(0, 2).Value
        wsFrm.Range("C15") = .Offset(0, 3).Value
        wsFrm.Range("C16") = .Offset(0, 4).Value
        wsFrm.Range("E16") = .Offset(0, 5).Value
        wsFrm.Range("B18") = .Offset(0, 6).Value
        wsFrm.Range("C18") = .Offset(0, 7).Value
        wsFrm.Range("D18") = .Offset(0, 8).Value
        wsFrm.Range("E18") = .Offset(0, 9).Value
        wsFrm.Range("F18") = .Offset(0, 10).Value
        wsFrm.Range("C30") = .Offset(0, 34).Value
        wsFrm.Range("C31") = .Offset(0, 35).Value
        wsFrm.Range("C33") = .Offset(0, 36).Value
        
        'option and checkbox
        wsFrm.Range("I22") = .Offset(0, 11).Value
        wsFrm.Range("I24") = .Offset(0, 12).Value
        wsFrm.Range("I25") = .Offset(0, 13).Value
        wsFrm.Range("I26") = .Offset(0, 14).Value
        wsFrm.Range("I29") = .Offset(0, 15).Value
        wsFrm.Range("I30") = .Offset(0, 16).Value
        wsFrm.Range("L24") = .Offset(0, 17).Value
        wsFrm.Range("L25") = .Offset(0, 18).Value
        wsFrm.Range("L26") = .Offset(0, 19).Value
        wsFrm.Range("L29") = .Offset(0, 20).Value
        wsFrm.Range("L30") = .Offset(0, 21).Value
        wsFrm.Range("O24") = .Offset(0, 22).Value
        wsFrm.Range("O25") = .Offset(0, 23).Value
        wsFrm.Range("O26") = .Offset(0, 24).Value
        wsFrm.Range("O27") = .Offset(0, 25).Value
        wsFrm.Range("O28") = .Offset(0, 26).Value
        wsFrm.Range("O29") = .Offset(0, 27).Value
        wsFrm.Range("O30") = .Offset(0, 28).Value
        wsFrm.Range("Q24") = .Offset(0, 29).Value
        wsFrm.Range("Q25") = .Offset(0, 30).Value
        wsFrm.Range("Q26") = .Offset(0, 31).Value
        wsFrm.Range("Q27") = .Offset(0, 32).Value
        wsFrm.Range("Q28") = .Offset(0, 33).Value
       
    End With
    
    ' check if earlier revision, if so lock sheet
    If cmbRevNo.ListIndex <> cmbRevNo.ListCount - 1 Then
        ProtectForm wsFrm, True
    Else
        UnProtectForm wsFrm, False
    End If
    Unload Me


about the code below can you explain because there is a minor problem in loading a data there are rev 0 and rev 1 but always rev 0 is loading in the form.. this vArr(1,1) rows and column in logsheet or in the form?
Code: 
    ReDim vArr(1 To 1, 1 To 5) ' columns B:G
    
    Set rFnd = wsLog.Cells(Me.StartRow, "B")
    Set rOut = wsFrm.Cells(22, "B")

    Do While rFnd.Value = cmbSubNo.Value And rFnd.Offset(0, 1).Value = cmbRevNo.Value
        With rFnd
            vArr(1, 1) = .Offset(0, 4).Value
            vArr(1, 3) = .Offset(0, 5).Value
            vArr(1, 4) = .Offset(0, 6).Value
            vArr(1, 5) = .Offset(0, 7).Value
            'vArr(1, 6) = .Offset(0, 8).Value
            
            'rOut.Resize(1, 2).Value = vArr
            Set rOut = rOut.Offset(1, 0)
            Set rFnd = .Offset(1, 0)
        End With
    Loop
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Excel cell format change without conditional formatting
Replies
5
Views
102
mumps
mumps
E
  • Question Question
Helps VBA code add "-new" to names that appear 3 times or more
Replies
2
Views
160
kvsrinivasamurthy
K
I
  • Question Question
Excel/Google Sheet Formula to extract client with unassigned Photographer
Replies
1
Views
193
Rhodie72
Rhodie72
J
  • Locked
  • Question Question
Forecast automatically Connected to CF
Replies
1
Views
113
Fluff
Fluff
S
  • Question Question
Help with tables
Replies
0
Views
183
SCPbrito
S

Forum statistics

Threads
1,223,276
Messages
6,171,138
Members
452,381
Latest member
Nova88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top