Solved

vb help

Posted on 2011-03-23
4
279 Views
Last Modified: 2012-05-11
Please can you help me change the code on my worksheet?

I need to change the code that is linked to the userform:-

next_empty.Offset(, 14) = txt1
next_empty.Offset(, 15) = txt2

At the moment textbox txt1 & txt2 input there values into "Data" columns 14 & 15

I want the dates in txt1 & txt2 to go to "Dates" (Sheet3) Columns B & C

The trouble is that the first occurrence on Data sheet occurs at line 14, I then need txt1 & txt2 to input -12 in order for them to appear on line 2

The current line (21) would hopefully into txt1 & txt2 to lines 9 "Dates"

Many thanks for your help

Private Sub cmdPopulateRow_Click()
    Sheets("Data").Unprotect Sheets("DataSheet").Range("C27").Value
    
    Dim next_empty As Range
    
    '~~> Get next empty row
    If Sheets("Data").Range("B14") <> "" Then
        Set next_empty = Sheets("Data").Range("B13").End(xlDown).Offset(1)
    Else
        Set next_empty = Sheets("Data").Range("B14")
    End If
    
    'fill in details
    next_empty = txtrecieveddate
    next_empty.Offset(, 2) = TextBox3.Value
    next_empty.Offset(, 3) = ComboBox1
    next_empty.Offset(, 4) = txtCustomerusername
    next_empty.Offset(, 5) = txtcustomersname
    next_empty.Offset(, 6) = TextBox5
    next_empty.Offset(, 7) = ComboBox2
    next_empty.Offset(, 8) = ComboBox3
    next_empty.Offset(, 9) = txtstarteddate
    next_empty.Offset(, 10) = txtcompleteddate
    next_empty.Offset(, 11) = "Yes"
    
    next_empty.Offset(, 14) = txt1
    next_empty.Offset(, 15) = txt2
    
    Sheets("Data").Protect Sheets("DataSheet").Range("C27").Value
    Unload Me
End Sub

Open in new window

0
Comment
Question by:Gazza83
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 35203257
Gary,

Can you pls post your file

For example I can't see how
txtrecieveddate
works

Cheers

Dave
0
 

Author Comment

by:Gazza83
ID: 35203298
Sorry Dave, my bad...forgot to upload the file.

Thanks,

Gaz
thursday.xls
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
ID: 35203375
pls change

next_empty.Offset(, 14) = txt1
 next_empty.Offset(, 15) = txt2


to this

hth

Dave

Sheets("Dates").Cells(next_empty.Row - 12, "B") = txt1
    Sheets("Dates").Cells(next_empty.Row - 12, "C") = txt2

Open in new window

0
 

Author Closing Comment

by:Gazza83
ID: 35203404
Thanks Dave, your code works perfectly as always.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question