Anyone can shrink this code?

[fucell]

is there a way to shorten this code ?

Range("b2").Value = frmquote.txtdate.Value
Range("b3").Value = frmquote.txtquote.Value
Range("b4").Value = frmquote.txtsalesman.Value
Range("b5").Value = frmquote.txtdterequired.Value
Range("b8").Value = frmquote.txtCompName.Value
Range("b9").Value = frmquote.txtadd1.Value
Range("b10").Value = frmquote.cmbo1.Value

frmquote.txtdate.enable = false
frmquote.txtquote.enable = false
frmquote.txtsalesman.enable = false
frmquote.txtdterequired.enable = false
frmquote.txtCompName.enable = false
frmquote.txtadd1.enable = false
frmquote.cmbo1.enable = false

thanks in advance.

 

[mikerickson]

You could wrap it in a With frmquote...End With.

 

[fucell]

ok this code is placed inside module1.inptdata

can you please give me idea how to do it. because this will work after clicking the command button. so all the data that has encoded in various textbox will go to each specific cell destination. can you show how to do it or atleast how to construct it.

thanks alot

fucell

 

[Carl Colijn]

Hi fucell,

The code can only be shrunk text-wise (not code-complexity wise) by using a With as mikerickson stated. To do so, you could use:

With frmquote
  Range("b2").Value = .txtdate.Value
  Range("b3").Value = .txtquote.Value
  Range("b4").Value = .txtsalesman.Value
  Range("b5").Value = .txtdterequired.Value
  Range("b8").Value = .txtCompName.Value
  Range("b9").Value = .txtadd1.Value
  Range("b10").Value = .cmbo1.Value
 
  .txtdate.enable = false
  .txtquote.enable = false
  .txtsalesman.enable = false
  .txtdterequired.enable = false
  .txtCompName.enable = false
  .txtadd1.enable = false
  .cmbo1.enable = false
End With

If you wanted to shrink the code to be able to update it faster (if this is just a small portion of the real code e.g.), then you could use a sub that sets the range and disables the control in one go;

Sub TakeValue(sAddress As String, ctlControl As Object)
  Range(sAddress).Value = ctlControl.Value
  ctlControl.Enabled = False
End Sub
 
...
 
' your code
Call TakeValue("b2", frmquote.txtdate)
Call TakeValue("b3", frmquote.txtquote)
Call TakeValue("b4", frmquote.txtsalesman)
Call TakeValue("b5", frmquote.txtdterequired)
Call TakeValue("b8", frmquote.txtCompName)
Call TakeValue("b9", frmquote.txtadd1)
Call TakeValue("b10", frmquote.cmbo1)

This will however only work if the enabledness of one control doesn't influence the next control's value in any indirect way (like via control event code). But it does have the added safety that you cannot forget to disable the control when grabbing it's value (in the original source the value-getting and disabling were seperated in two distinct blocks of code which is easy to misalign).

Hope this helps,
Carl Colijn

 

[Norie]

If you just want to shorten the code.

Dim arrAdrs
Dim arrCtrls
Dim I As Long
 
    arrAdrs = Split("B2,B3,B4,B5,B8,B9,B10", ",")
    arrCtrls = Split("txtdate, txtquote, txtsalesman, txtdterequired, txtCompName, txtadd1, cmbo1", ",")
    
    For I = LBound(arrAdrs) To UBound(arrAdrs)
    
        Range(arrAdrs(I)).Value = frmquote.Controls(arrCtrls(I)).Value
        frmquote.Controls(arrCtrls(I)).Enabled = False

    Next I

 

[fucell]

nice one! very informative reply! well I would to try that a function sub that sets the range and disables the control in one go.

from where should I place this sub function ?

Sub TakeValue(sAddress As String, ctlControl As Object)
  Range(sAddress).Value = ctlControl.Value
  ctlControl.Enabled = False
End Sub

inside the command button.click or i will just put it in one module and call it using commandbutton?

thanks a lot anyway! hoping for your quicky response. ^__^

 

[fucell]

thank you Mr. Norie i will use your code for sure.

 

[Norie]

Fucell

Are these controls on a userform?

If they are why is this code in a standard module?

How are you calling it?

If it's being called from the click of a button on a userform it would usually go in the userform module.

 

[fucell]

Fucell

Are these controls on a userform?

If they are why is this code in a standard module?

How are you calling it?

If it's being called from the click of a button on a userform it would usually go in the userform module.

yes! im using a form. so basically i have a form with numerous textbox. I designed that form for my co-worker to just encode all information and paste those encoded info from textbox to specific excel cells.
for example:
the frmquote is a form
the txtdate = is textbox

so he will encode the data/info to txtdate and click the button

so since after clicking the commandbuttong.click this code will activated

ActiveWorkbook.Worksheets("EngInfo").Select
Range("b2").Value = frmquote.txtdate.Value
frmquote.txtdate.enable = false
end sub

'but that just an example, i just put 1 textbox but mainly I have 20 of those.

hope you get it.

 

[Norie]

That sort of code would normally go in the userform module rather than a separated module.

It doesn't need to but if it does then it's a lot easier to work with.