What is CHR, txt,etc??

[romij29]

Can anyone please explain to a VB Newbie what a Chr is and when it is used??Also how do I know which numbers to use if selecting a specific colour.To make it easy here's a piece of code that's got me baffled!!

Cells(65, txtPeriod + 1).Formula = "=" & Chr(65 + txtPeriod) & 9 & "/" & Chr(65 + txtPeriod) & 4
Cells(66, txtPeriod + 1).Formula = "=" & Chr(65 + txtPeriod) & 10 & "/" & Chr(65 + txtPeriod) & 4
Cells(67, txtPeriod + 1).Formula = "=" & Chr(65 + txtPeriod) & 19 & "/" & Chr(65 + txtPeriod) & 9
Cells(68, txtPeriod + 1).Formula = "=" & Chr(65 + txtPeriod) & 20 & "/" & Chr(65 + txtPeriod) & 10

txtPeriod was placed as an argument ByRef(txtPeriod) where period is each month and has a column p1, p2....p12

This bit refers to formulas in specific cells..What does "txtPeriod + 1" and "Chr(65 + txtPeriod)" mean exactly??

Also I ve a colour index code supposedly to colour a certain area on a spreadsheet in excel. a piece of code for you:

Selection.Interior.ColorIndex = 34

How do I know which number fits which colour??

Thanks

 

[romij29]

I think it might have something to do with the ByRef txtperiod argument in the Public Sub Procedure.But not sure.Any takers on this one??

 

[romij29]

The txtPeriod has not been declared at all in the "Option explicit" area.However I know it refers to a col of P$L item descriptions(Turnover, Profit,etc.)so how can this be defined and how would it be defined.An integer,array,...any ideas??Has it got anything to do with the failure to step into the program??
Thanks

 

[mrmovie]

change the ByRef txtPeriod at least (ByRef txtPeriod As Integer) and see if you get a runtime

 

[xlbo]

Nope - you can't step through a sub if it expecting an argument to be passed to it

Create another sub and call the sub you want to step through from there - either by stepping through the 1st sub until it calls the 2nd or by setting a breakpoint as the 1st line in the 2nd sub eg

Sub Let_Me_Step_Thru()
By_Region(1)
End sub


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[mrmovie]

thanks xlbo, im not sure im helping anyone out apart from myself!!! not a bad thing

 

[xlbo]

I find it's the best way to learn !

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[mrmovie]

with regards the declaration of txtPeriod, you define it when you put it in the Sub declaration

So

Sub go_Data(ByRef txtPeriod)

means you are declaring it as a VARIANT, for use within the scope of the Sub, so to speak.
if you want to declare it as a type then use

go_Data(ByRef txtPeriod As Integer)

i think we have confirmed it should be an integer???

somewhere else in your code you will have

Call go_Data(intX)
or
go_Data(5)
or
go_Data(UserForm1.txtField1.Value)

 

[romij29]

You guys have been of immense help!I have managed to step thru it using
Sub Let_Me_Step_Thru()
By_Region(1)
End sub

and have realised (dont laugh!!still learning)that txtPeriod only relates to the first column.so say,in order to put date from a source worksksheet into say P12 of the destination worksheet,I changed this bit of the macro:

Cells(4, txtPeriod + 1).Activate
ActiveCell.PasteSpecial (xlPasteValues) to Cells(4, txtPeriod + 12) and it worked.

I also realised thaat the code writer refernce to txt Period came from a form which allowed the user to click on a button labelled "Form"(when running the macro)which allows another form to pop up with a variety of specific commands including a textbox labelled Period (where the user would put in say the Period say P1,p12 etc).But its a bit incomplete and I am totally lost.However I think that's how its supposed to work.I think that if you put in the specific month you want to run reports,it then goes to the ByRef txtPeriod and the macro runs. Here's the various procedures for the form(or dialogue box ).

Option Explicit
------
Private Sub Canc_Click()
Unload Me 'For Cancel Button on dialogue box
End Sub
------
Public Sub Create_Click() ' for macro RUN button
Dim i As Integer, intRow As Integer

Workbooks("Control.xls").Sheets("Control").Range("J2:J20").Clear
intRow = 2
i = 0

'Inserts the name of a selected template(s) in column J
'for the macro to refer to
Do While Range("F" & i + 2) = "" = False
If lstRegions.Selected(i) = True Then
Range("J" & intRow) = Range("G" & i + 2)
intRow = intRow + 1
End If
i = i + 1
Loop

'tests which tick boxes have been selected and calls the appropriate
'procedure to start copying the data
If Forecasts.Value = True And cbClose = True Then
By_Region (txtPeriod)
Save
ElseIf Forecasts.Value = True Then
By_Region (txtPeriod)
Else
MsgBox ("Please select at least one option"), vbCritical
End If
Trad_Accs.Hide
End Sub
-----------
Private Sub Forecasts_Click()
'This is a "Forecasts" label
End Sub ' with a tickbox on side
------
Private Sub lstRegions_Change()
cbClose.Enabled = True 'This comes up when I click into an
End Sub 'empty area under "Forecasts" label
------------
Private Sub MultiPage1_Change()

End Sub
------------
Private Sub txtPeriod_Change()

End Sub ' For the specific Period to be written
----------------
Private Sub UserForm_Initialize()
'Will load user interface lists in to the form when the form is
'displayed

Dim i As Single
Static arrForm()
Static arrFormMaint()
i = 2

Do Until Range("F" & i) = ""
ReDim Preserve arrForm(i - 2)
arrForm(i - 2) = Workbooks("Control.xls").Sheets("Control").Range("F" & i)
i = i + 1
Loop
lstRegions.List() = arrForm

cbClose.Enabled = False
'sort data in control sheet by column C in order
'for the macro to run correctly
Range("A21000").Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending
Range("F2:H1000").Select
Selection.Sort Key1:=Range("G2"), Order1:=xlAscending
Range("A1").Activate

Dim strLine As String, strFormat As String
i = 2

'Sets a format for the cost centres inclusive of leading
'00000 in order for the macro to perform accurately
Do Until Workbooks("Control.xls").Sheets("Control").Range("A" & i) = ""
strLine = Workbooks("Control.xls").Sheets("Control").Range("A" & i)
strLine = Format(strLine, "'00000")
Workbooks("Control.xls").Sheets("Control").Range("A" & i) = strLine
i = i + 1

Loop
End Sub


The last procedure I have no idea about!!Need help on this one!!
Thanks