Posts by pragov

pragov · Nov 14th 2017

Hi All,

I have a report which contains a service type field, which is column D, with values such as

SERVICE Type

GUIDE
CONNECT
FOLLOW

I have a program with a hyperlink

Code

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http://test.values.com?tool=Hlink&sc=testing\/primary&ph=" & Range("K" & rw).Value _
& "&stme=" & Range("B" & rw).Value & "&etme=" & Range("C" & rw).Value, TextToDisplay:="HLINK"

The table, HlinkValue contains values such as

SERVICE Type FOLDER
GUIDE primary
CONNECT bin
FOLLOW secondary

What I need help is for my code to identify is, if I am on row 1 and the value of service type is GUIDE, then the value of sc should be testing\/prime or if it is CONNECT, the value should be testing\/bin.

Code

With ActiveSheet
col = FindCol("SERVICE TYPE", 1, .Name)
LastRow = .Range("A1").CurrentRegion.Rows.Count
If col > 0 Then
For sc = 2 To LastRow
Cells(sc, col).Select
.................................
.................................
Next rw
End If
End With

Display More

Thanks,

Pragov

pragov · Nov 6th 2017

I was able to resolve my issue of inserting a column after a report was created as well as including a hyperlink.

Now, how do I do this

For i to LastRow
Str = “http://" & range(“URLAddress”).value &"sc=" testing & "ph=" & cell_that_contains_phoneNo & "st=" & cell_that_contains_starttimevalue & "et=" & cell_that_contains_endtimevalue

Range("A” & i").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address=Str, TextToDisplay:="CONNECT"
Next i

How do I read the sc, ph, st and et values?
sc: cell that contains service type (if the value is GUIDE, then sc = testing)
ph :cell_that_contains_phoneNo - 6th field
st:cell_that_contains_starttimevalue - 2nd field
et:cell_that_contains_endtimevalue - 3rd field.

How would I refer to the cells 2nd, 3rd and 6th in my Address?

pragov · Nov 6th 2017

I modified my code as

'Hlink Code

Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Value = "Hlink"

With ActiveSheet
col = FindCol("Hlink", 1, .Name)
LastRow = .Range("A1").CurrentRegion.Rows.Count
If col > 0 Then
X = .Range(.Cells(1, col), .Cells(LastRow, col))
For rw = 2 To LastRow

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"http://testing.com?tool=HLINK" _
, TextToDisplay:="CONNECT"

Next rw
.Range(.Cells(1, col), .Cells(LastRow, col)) = X
End If

End With

I am getting [FONT="Calibri"]an “invalid call or procedure” error for the Hyperlink[/FONT]

pragov · Nov 6th 2017

Hi All,

This is a two part question.

I have an existing report to which I need to insert an hyperlinked field at "A1" called Hlink.

I need to insert a column in before col B and name it "Hlink". Also, I need to have this column as hyperlinked and the text called "CONNECT"
I am not sure of how to use the OFFSET effectively.

The second part is:

for the address, I need to read values from the content of other fields in the report as in

This is part of the hyperlink code, I am trying to use

For i to LastRow
Str = “http://" & range(“URLAddress”).value &"sc=" testing & "ph=" & cell_that_contains_phoneNo & "st=" & cell_that_contains_starttimevalue & "et=" & cell_that_contains_endtimevalue

Range("A” & i").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address=Str, TextToDisplay:="CONNECT"
Next i

How do I read the sc, ph, st and et values?
sc: cell that contains service type (if the value is GUIDE, then sc = testing)
ph :cell_that_contains_phoneNo - 6th field
st:cell_that_contains_starttimevalue - 2nd field
et:cell_that_contains_endtimevalue - 3rd field.

I am attaching a sample report with bare minimum code.

Thanks,

Pragov

pragov · Jun 1st 2016

Re: Formatting dates

I was able to solve it

Code

Date=     Format(Mid(A1, 1, 19), "yymmdd")
   Hour=     Format(Mid(A1, 1, 19), "Hh")
   Minute=     Format(Mid(A1, 1, 19), "Nn")

pragov

pragov · Jun 1st 2016

Re: Formatting dates

How do I do this in VBA?
TEXT and FIND work in Formula, but not in VBA.

pragov · May 31st 2016

Hi All,

I have a value of the field, ProcessDate as 2016-05-18 19:27:40

I need to format 2016-05-18 as 1160518 - Pdate and 19:27 to 19 - PHour and 27 - Pmin

How do I do this in one single function or in three steps using VBA?

[TABLE="width: 547"]

[tr]

[/tr]

[/TABLE]
[TABLE="width: 124"]

[tr]

[TD="class: xl82, width: 124"]Thanks,
pragov
[/TD]

[/tr]

[/TABLE]

pragov · Apr 22nd 2016

Re: Clickable link on cells

How to mimic the hyperlink?

pragov · Apr 22nd 2016

Re: Clickable link on cells

The field will contain code, which allows users to bring up data related to this field, when they click on it.
This is an existing field, which was not used in this manner. It is being explored as field that can be viable.

Can you let me know how to implement?

pragov · Apr 22nd 2016

Hi All,

Is it possible to create click able cells, similar to hyperlinks using excel vba?
The issue is my field doesn’t have a “:” or “@” that can be formatted as a hyperlink.

How to reproduce a functionality similar to hyperlink, with the blue underline?
Or any alternative?

Thanks,
pragov

pragov · Jun 4th 2015

Re: shift columns

Hi,
I get an error on line
ActiveSheet.Columns("H").Delete

It says that excel resources cannot perform this action.
The first two lines were done.

Thanks,
pragov

pragov · Jun 3rd 2015

Hi All,

I have a report with columns AA to AZ.

After I run the query and during formatting the report, I need to shift Col G to col A first and then col F to col B.

How would I use the Insert Col before A, Select Col G, do the shift left? Then again do a insert column after A and select col F and shift to left to col B.

so Now col G becomes col A, col F becomes col B and col A becomes col C.

I am aware of this but run into errors.

Thanks,

pragov

pragov · Jun 3rd 2015

Hi All,

I am using two listboxes to select items to be displayed on a report. I bring the form containing the two listboxes from the parent form's "Prudt" command button.
I use CR.Show and the list box form appears.

The navigation buttons such as add, remove etc work well initially. But after a while the Add and Rem events are not possible.It seems as if the listboxes need to be refreshed.But the AddAll and RemAll work well.
This does not happen when the form("Prudt") is initially opened.

..

Code

Private Sub cmdAdd_Click()
Dim iCtr As Long


    Application.EnableEvents = False
    Application.ScreenUpdating = False
   
    If Me.lstBox1.ListIndex = -1 Then Exit Sub


    For iCtr = 0 To Me.lstBox1.ListCount - 1
        If Me.lstBox1.Selected(iCtr) = True Then
            Me.lstBox2.AddItem Me.lstBox1.List(iCtr)
        End If
    Next iCtr


    For iCtr = Me.lstBox1.ListCount - 1 To 0 Step -1
        If Me.lstBox1.Selected(iCtr) = True Then
            Me.lstBox1.RemoveItem iCtr
        End If
    Next iCtr
    Application.EnableEvents = True
End Sub

Display More

Code

Private Sub cmdRem_Click()
Dim iCtr As Long


    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If Me.lstBox2.ListIndex = -1 Then Exit Sub
    
    For iCtr = 0 To Me.lstBox2.ListCount - 1
        If Me.lstBox2.Selected(iCtr) = True Then
            Me.lstBox1.AddItem Me.lstBox2.List(iCtr)
        End If
    Next iCtr


    For iCtr = Me.lstBox2.ListCount - 1 To 0 Step -1
        If Me.lstBox2.Selected(iCtr) = True Then
            Me.lstBox2.RemoveItem iCtr
        End If
    Next iCtr
    
    Application.EnableEvents = True
    
End Sub

Display More

Is it a refresh issue or anything else? What's confusing is,the first time when the forms are open,all these navigations work.

Thanks

pragov

pragov · May 21st 2015

Re: Populating ListBox

I already tried that, but it gives errors

Thanks,

pragov

pragov · May 21st 2015

Hi All,

I am using this code, to populate a range used as a rowsource for listbox.

Code

ResetSht "MRList"
    With Sheets("MRList")
        i = 1
        'db_MRList
        'For Each cel In Range("MRList")
        For Each cel In Range("db_MRList")
            If cel.Value <> "All MR" Then
                .Cells(i, 1).Value = cel.Value
                i = i + 1
            End If
        Next cel
        .Range("A1").Value = "DeviceType03"
        .Range("A1").Name = "DeviceType03"
        .Range("G1").Value = "DeviceType04"
        .Range("G1").Name = "DeviceType04"
        .Visible = False
    End With

Display More

But it skips the first field and populates other fields starting from the second one.
Not sure why?

Secondly, If I need to restrict the entries in my ListBox to Unique values, how would I do that?

Thanks,

Pragov

pragov · Apr 15th 2015

Re: Transfer from ListBox to TextBox

When I do the above way,

Code

Private Sub cmdSubmit_Click()
Dim lItem As Long
Dim allItems As String
  
For lItem = 0 To lstBox2.ListCount - 1
   'allItems = allItems & lstBox2.List(lItem)
    allItems = allItems & "," & lstBox2.List(lItem)
Next lItem


AdhocQuery.txtboxFields.Value = allItems
Me.Hide
End Sub

Display More

I am getting the result in the textbox as
,CREATION TS,Global CallID CallManagerId,nodeId,PROCESS TS.

why is the comma appearing before the first field?

pragov · Apr 15th 2015

Re: Navigation in List Box

I do have AddItem to populate the ListBox,as per my code above.
Also, This is what I am trying to figure:
When I bring up the form, the list box is populated automatically, which I am confused, since the rowsource is set based on combobox.
When I change the category, the list box gets populated but fails when I try to add items.

pragov · Apr 15th 2015

Hi All,

I tried posting this before, but it failed because I did not tag it
I am using a combobox above a list box. The combobox has a list of 5 categories. Each category has few fields associated to it.
The category list and the fields associated with each category are populated in a worksheet with a named range.

I have two list boxes.The first list box rowsource is based on the selection of the combobox category.

When I bring up the form, the list box is populated automatically, which I am confused, since the rowsource is set based on combobox.
When I change the category, the list box gets populated but fails when I try to add items.

I have a ADD and a Remove button to add or remove items between two list boxes.
When I try to add items to the second list box, it fails at the RemoveItem i line.
[
.....
Me.lstBox1.RemoveItem i
...
]

Code

Option Explicit
'Move ListBox Items code
Private Sub cboChooseFields_Change()
If cboChooseFields.Value = "Frequently Used Fields" Then
    lstBox1.RowSource = "CDRFreqList"
ElseIf cboChooseFields.Value = "All Fields" Then
    lstBox1.RowSource = "CDRALLFieldsList"
ElseIf cboChooseFields.Value = "IP Address Fields" Then
    lstBox1.RowSource = "CDRIPAddrFieldsList"
    ElseIf cboChooseFields.Value = "Date/Time Fields" Then
    lstBox1.RowSource = "CDRDateTimeList"
ElseIf cboChooseFields.Value = "Partition and Number Fields" Then
    lstBox1.RowSource = "CDRPartitionList"
End If
End Sub
Private Sub LoadStateListBox(Optional LstBox As String = "All")
    Dim cel As Range
    Dim i As Integer
    Dim disableOption As Boolean
    
    Select Case LstBox
    Case "DeviceType01", "All"
        Me.lstBox1.Clear
        For Each cel In Range("DeviceType01").CurrentRegion
            If cel.Value <> "DeviceType01" Then Me.lstBox1.AddItem cel.Value
        Next cel
    End Select
    Select Case LstBox
    Case "DeviceType02", "All"
        Me.lstBox2.Clear
        For Each cel In Range("DeviceType02").CurrentRegion
            If cel.Value <> "DeviceType02" Then Me.lstBox2.AddItem cel.Value
        Next cel
    End Select
 End Sub




Private Sub StateOptionClick(ab As String)
    Dim i As Integer
    Dim cel As Range
    
'    Range("DeviceType01").CurrentRegion.ClearContents
'    Range("Devicetype01" & ab).CurrentRegion.Copy Range("DeviceType01")
    Range("DeviceType01").Value = "DeviceType01"
    LoadStateListBox
    
    For i = Me.lstBox2.ListCount - 1 To 0 Step -1
        Set cel = Range("DeviceType01").CurrentRegion.Find(What:=lstBox2.List(i), _
            after:=Range("DeviceType01"), MatchCase:=True, LookAt:=xlWhole)
        If cel Is Nothing Then
        Else
            lstBox1.RemoveItem cel.Row - 2
            Range("DeviceType01").Offset(cel.Row - 1, 0).Delete Shift:=xlUp
        End If
    Next i
End Sub


Private Sub cmdAdd_Click()
Dim i As Integer
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If Me.lstBox1.ListIndex = -1 Then Exit Sub
    
    For i = Me.lstBox1.ListCount - 1 To 0 Step -1
        If Me.lstBox1.Selected(i) = True Then
            ' Add, sort and load
            Me.lstBox2.AddItem Me.lstBox1.List(i)
            With Range("DeviceType02")
                .Offset(1, 0).Insert Shift:=xlDown
                .Offset(1, 0).Value = Me.lstBox1.List(i)
                .CurrentRegion.Sort Key1:=Range("DeviceType02"), Order1:=xlAscending, Header:=xlYes
            End With
            ' Remove from list
            Range("DeviceType01").Offset(i + 1, 0).Delete Shift:=xlUp
            Me.lstBox1.RemoveItem i
            'Reload to place in sort order
            LoadStateListBox "DeviceType02"
        End If
    Next i
    'DisplayFr
    Application.EnableEvents = True
End Sub


Private Sub cmdAddAll_Click()
     
Dim i As Long
    With Me.lstBox1
        'Loop through the items
        For i = 0 To .ListCount - 1
            'Add each item to the other listbox
            Me.lstBox2.AddItem .List(i)
        Next i
        'Clear the items from the current listbox
        .Clear
    End With


End Sub


Private Sub cmdCancel_Click()
    Me.Tag = "Cancel"
    Me.Hide
End Sub


Private Sub cmdMoveDown_Click()
If Me.lstBox2.ListCount = 0 Then Exit Sub
    If Me.lstBox2.ListIndex = Me.lstBox2.ListCount - 1 Then Exit Sub
    Dim i As Long, j As Long, valtoadd As String, bl As Boolean
    bl = False


    For i = 0 To Me.lstBox2.ListCount - 1
        If Me.lstBox2.Selected(i) = True Then
            valtoadd = Me.lstBox2.Column(0, i)
            Me.lstBox2.RemoveItem (i)
            bl = True
            j = i + 1
        End If
    Next
    If bl = False Then Exit Sub
    Me.lstBox2.AddItem valtoadd, j
    Me.lstBox2.ListIndex = j
End Sub


Private Sub cmdMoveUp_Click()
If Me.lstBox2.ListCount = 0 Then Exit Sub
    If Me.lstBox2.ListIndex = 0 Then Exit Sub
    
    Dim i As Long, j As Long, valtoadd As String, bl As Boolean
    bl = False
    For i = 0 To Me.lstBox2.ListCount - 1
        If Me.lstBox2.Selected(i) = True Then
            valtoadd = Me.lstBox2.Column(0, i)
            Me.lstBox2.RemoveItem (i)
            bl = True
            j = i - 1
        End If
    Next
    If bl = False Then Exit Sub
    Me.lstBox2.AddItem valtoadd, j
    Me.lstBox2.ListIndex = j
End Sub


Private Sub cmdRem_Click()
Dim i As Integer
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If Me.lstBox2.ListIndex = -1 Then Exit Sub
    
    For i = Me.lstBox2.ListCount - 1 To 0 Step -1
        If Me.lstBox2.Selected(i) = True Then
            ' Add, sort and load
            Me.lstBox1.AddItem Me.lstBox2.List(i)
            With Range("DeviceType01")
                .Offset(1, 0).Insert Shift:=xlDown
                .Offset(1, 0).Value = Me.lstBox2.List(i)
                .CurrentRegion.Sort Key1:=Range("DeviceType01"), Order1:=xlAscending, Header:=xlYes
            End With
            ' Remove from list
            Range("DeviceType02").Offset(i + 1, 0).Delete Shift:=xlUp
            Me.lstBox2.RemoveItem i
            'Reload to place in sort order
            LoadStateListBox "DeviceType01"
        End If
    Next i
   'DisplayFr
    Application.EnableEvents = True
End Sub


Private Sub cmdRemAll_Click()
  
    
     Dim i As Long
   
    With Me.lstBox2
        For i = 0 To .ListCount - 1
            Me.lstBox1.AddItem .List(i)
        Next i
        .Clear
    End With
End Sub




Private Sub UserForm_Activate()
    StateOptionClick "b"
End Sub

Display More

I guess, the list box should be cleared,but I have no idea.

Thanks,

pragov · Apr 14th 2015

Re: Transfer from ListBox to TextBox

Thanks a lot.Appreciate your help.

pragov · Apr 14th 2015

Re: Transfer from ListBox to TextBox

On my parent form, frmReport I have this textbox,txtboxFields. If I run your code on the Child form's submit button,then how does it get transferred to the txtbox of the Parent form?

Code

Private Sub cmdSubmit_Click()
Dim lItem As Long
Dim allItems As String
  
For lItem = 0 To lstBox2.ListCount - 1
    allItems = allItems & lstBox2.List(lItem)
Next lItem


End Sub

Posts by pragov

Seach values

Insert a hyperlink field to an existing report

Insert a hyperlink field to an existing report

Insert a hyperlink field to an existing report

Formatting dates

Formatting dates

Formatting dates

Clickable link on cells

Clickable link on cells

Clickable link on cells

shift columns

shift columns

ListBox Refresh

Populating ListBox

Populating ListBox

Transfer from ListBox to TextBox

Navigation in List Box

Navigation in List Box

Transfer from ListBox to TextBox

Transfer from ListBox to TextBox