Data Capture for UserForm

[cheffy]

Hi,
I have been struggling with this problem for quite some time. I have a Userform which captures data and puts into the appropriate cells in two different sheets in my file (the data captured is exactly the same but I need it in two different sheets to perform subsequent calculations). One of the input fields is a number, in one sheet (Summary) the calculations work fine, but the other sheet (Data) I have format issues therefore I can't perform any calculations.

How do I change the format of the number captured in the "Data" sheet, but don't change the format captured in the "Summary" sheet?

The field in the userform which captures the number is called "Reg1" - this is a random auto generated number.

I have tried added the following code in the "Data" sheet capture area but is still does not work,

Reg1.Value = CDbl(rngNext)

Does anybody know how I can achieve this?

Any help would be much appreciated.

The full code is below.

Private Sub CommandButton1_Click()
Unload UserForm1
UserForm3.Show vbModeless
UserForm3.Repaint
Dim ws As Worksheet
Dim rngNext As Range

Set ws = Worksheets("Data") '

Set rngNext = ws.Range("T" & Rows.Count).End(xlUp).Offset(1)


rngNext .Value = Reg1.Value ' column T
Reg1.Value = CDbl(rngNext)

rngNext.Offset(, 1).Value = Reg2.Value ' column U

rngNext.Offset(, 2).Value = Reg3.Value ' column V

rngNext.Offset(, 5).Value = Reg4.Value ' column Y

rngNext.Offset(, 17).Value = Reg5.Value ' column AK

If Reg6.Value = True Then rngNext.Offset(, 13).Value = 1 ' column AG

If Reg6.Value = False Then rngNext.Offset(, 13).Value = "" ' column AG

If Reg7.Value = True Then rngNext.Offset(, 14).Value = 1 ' column AH

If Reg7.Value = False Then rngNext.Offset(, 14).Value = "" ' column AH

If Reg8.Value = True Then rngNext.Offset(, 15).Value = 1 ' column AI

If Reg8.Value = False Then rngNext.Offset(, 15).Value = "" ' column AI

If Reg9.Value = True Then rngNext.Offset(, 16).Value = 1 ' column AJ

If Reg9.Value = False Then rngNext.Offset(, 16).Value = "" ' column AJ
Unload UserForm3
UserForm4.Show vbModeless
UserForm4.Repaint

Set ws = Worksheets("Summary") '
Sheet1.Unprotect Password:="xxxxxx"

Set rngNext = ws.Range("B" & Rows.Count).End(xlUp).Offset(1)


rngNext.Value = Reg1.Value ' column B

rngNext.Offset(, 4).Value = Reg2.Value ' column F

rngNext.Offset(, 3).Value = Reg3.Value ' column E

rngNext.Offset(, 2).Value = Reg4.Value ' column D

rngNext.Offset(, 12).Value = Reg5.Value ' column N

If Reg6.Value = True Then rngNext.Offset(, 7).Value = 1 ' column I

If Reg6.Value = False Then rngNext.Offset(, 7).Value = "" ' column I

If Reg7.Value = True Then rngNext.Offset(, 8).Value = 1 ' column J

If Reg7.Value = False Then rngNext.Offset(, 8).Value = "" ' column J

If Reg8.Value = True Then rngNext.Offset(, 9).Value = 1 ' column K

If Reg8.Value = False Then rngNext.Offset(, 9).Value = "" ' column K

If Reg9.Value = True Then rngNext.Offset(, 10).Value = 1 ' column L

If Reg9.Value = False Then rngNext.Offset(, 10).Value = "" ' column L
Sheet1.Protect Password:="xxxxxx"

' unload and reshow form for further input - change UserForm1 if
' your form name is different
Unload UserForm4
UserForm5.Show vbModeless
UserForm5.Repaint
Application.Wait (Now + #12:00:02 AM#)
Unload UserForm5
Application.Calculation = xlCalculationAutomatic
MsgBox "This adviser has been added to your plan"

Unload Me
UserForm1.Show
Application.Calculation = xlCalculationManual
End Sub

 

[dmt32]

Hi,
Use the Val Function to coerce whats coming out of your textbox - this should change the textbox string to a numeric value of the appropriate type.

 rngNext.Value = Val(Reg1.Value)

Dave

 

[cheffy]

Thanks for the suggestion Dave, I tried it and but it still does not work.

The number captured in the "Summary" sheet works fine (it always has done) - I checked the format of this cell after it was captured and it was 'General'.
The number captured in the "Data" sheet does not work, the format in this cell is still "Text".

To give you an idea on what I am doing with this cell....I am linking to the cell in the "Data" sheet and then perform a vlookup off this link (eg if the cell captured from the Userform is G1, A1 has the formula (=if(G1="","",G1) - I then perform a vlookup off cell A1), however the vlookup does not work!

The only possible solution I can think of is to manually change the format of the cells in the "Data" sheet to 'General' beforehand, however this is not ideal and would prefer to not have to do this.

 

[jim may]

FYI.. sample. Codingf
ActiveSheet.Range("A5").NumberFormat = "General"

 

[dmt32]

Hi,
You can apply cell formatting in your code e.g.

    With rngNext
        .Value = Val(Reg1.Value) ' column T
        .NumberFormat = "General"
    End With

if still having problems then always helpful if you could place copy of your workbook with sample data in a dropbox & provide link to it.

Dave

 

[cheffy]

That works (combining the two - the latest suggestion from Dave).

Thanks both for your help...much appreciated.