Posts by wxchic

wxchic · Apr 30th 2014

Re: VBA Set new IE tab as variable

The code I've got for the username and password elements works. It will log in and submit my username and password correctly to each site. It will run thru each website I have and correctly login to each. The problem is each new site is overwriting the previous one in the same window, thus my need for tabs. I would like to reference each newly opened tab right after it opens and then perform the login.

Code

With IE 
            .Visible = True 
            .Navigate "www.2ndtab.com[/URL]",CLng(navOpenInNewTab) 
            Do Until.ReadyState = 4 
                DoEvents 
            Loop

How do I use this part/object that was just created below?

Code

.Navigate [URL="http://www.2ndtab.com/"]www.2ndtab.com[/URL]",CLngnavOpenInNewTab) = somevariable

Then use:

Code

IE.somevariable.all.Item("userid").Value = cUsername 
            IE.somevariable.all.Item("password").Value = cPassword 
            IE.somevariable.forms(0).submit

Can I reference the newly created tab, do the work on the said tab, then move on to next tab, etc.?

wxchic · Apr 29th 2014

I am opening an instance of IE thru Excel. I have several tabs that need to open after the first with each having their own login info. Each tab will be populated with the login info and subsequently logged in.

It is opening a new tab, but getting hung up on this line: .Document.all.Item("username").Value = cUsername

I suspect it doesn't like the .document.all.item that I have. Is there a way I can assign the new tab that is opened to a variable like,

newesttab.all.item("userid").value = cUsername
newesttab.all.item("password").value = cPassword

Here is my code:

Code

Private Sub CommandButtonLOGIN_Click()
' This enters information from the Userform to the database
    Unload Me
    cUsername = Me.txtPPR.Value
    cPassword = Me.txtPASSWORD.Value
    cPPR = 0 & Me.txtPPR.Value & 0 & 0




'open window


Set IE = CreateObject("InternetExplorer.Application")
Const navOpenInNewTab = &H800
        Dim address As String 'Create a variable for our address
        With IE
            .Visible = True
            .Navigate "[URL="http://www.firstpage.com"]www.firstpage.com[/URL]"
        End With
        
'open 2nd tab 


   If cbALL.Value = True Then
        With IE
            .Visible = True
            .Navigate "[URL="http://www.2ndtab.com"]www.2ndtab.com[/URL]", CLng(navOpenInNewTab)
            Do Until .ReadyState = 4
                DoEvents
            Loop
            .Document.all.Item("userid").Value = cUsername
            .Document.all.Item("password").Value = cPassword
            .Document.forms(0).submit
        End With


'open 3rd tab
            
        With IE
            .Visible = True
            .Navigate [URL="http://www.3rdtab.com"]www.3rdtab.com[/URL], CLng(navOpenInNewTab)
            Do Until .ReadyState = 4
                DoEvents
            Loop
            .Document.all.Item("userid").Value = cUsername
            .Document.all.Item("password").Value = cPassword
            .Document.forms(0).submit
        End With


 


'login part


 If cbAMDS.Value = True Then
        With IE
            .Visible = True
            .Navigate "www.[URL="http://www,"]2ndtab.com[/URL]"
            Do Until .ReadyState = 4
                DoEvents
            Loop
           .Document.all.Item("username").Value = cUsername
            .Document.all.Item("password").Value = cPassword
             .Document.forms(0).submit
       While IE.busy
       DoEvents
       Wend
        End With
    End If
    
    '''''''''''''''''''''''''''''''LOGINS IN TO AOS
    If cBAOS.Value = True Then
        With IE
            .Visible = True
            .Navigate "[URL="http://www.3rdtab.com"]www.3rdtab.com[/URL]"
            Do Until .ReadyState = 4
                DoEvents
            Loop
            .Document.all.Item("userid").Value = cUsername
            .Document.all.Item("password").Value = cPassword
                .Document.forms(0).submit
            End With
        Application.Wait (Now + TimeValue("0:00:05"))
    End If

Display More

wxchic · Sep 23rd 2013

Re: Drop down list

That worked great. Is there any way to show that it is a drop down list even if you don't have the cell selected?
And any way to always show the first cell in the range?

Thanks for your help.

wxchic · Sep 23rd 2013

Re: Drop down list

They will not be mixed. Only zeros at the bottom.

Range:

Data
0
0
0
0
0
0
0
0
0
0

or sometimes:

Data1
Data2
Data3
0
0
0
0
0
0
0
0

wxchic · Sep 23rd 2013

Hello,

I'm trying the create a drop down list in a cell. The list source is a range with 20 rows. All cells in the range may be populated. At times only a few will be populated. How do I get the drop down list to show only the cells that have data in them, not the zeros in the range?

Also can I always have the first cell in the range show on the drop down list?

Thanks for any help.

wxchic · Jul 3rd 2013

Hello,

I would like to compare and consolidate rows based on equal cells in the rows. Something like this:

If
(row1, columnC) = (row2, columnC) AND
(row1, columnH) = (row2, columnH) AND
(row1, columnK) = (row2, columnK) Then

keep row1 columns A,B,C,E,G,H,J,K cells the same
concatenate row1 and row2 columns D,F,I with "/"

i.e. row1, columnD = CONCATENATE(row1,columnD), "/", (row2,columnD)

If first condition is not met you can move down and compare the next 2 rows. I would like it to loop through several rows.

If you get to empty row, exit.

Thanks for any help.

I tried to upload example file but got an error.

wxchic · May 16th 2013

I'm trying to set the print area in vba using a dynamic cell's row (firstbcity) and a static range of columns(A:AB). Problem is I don't know how to specifiy the range correctly. Something like:

Code

Dim firstbcity As Range




Set firstbcity = citylist.Find(what:="", LookIn:=xlValues, LookAt:= _
xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)


ActiveSheet.PageSetup.PrintArea = Range("firstbcity's row:A:AB")

Display More

How do I write the Range correctly?

Thanks for any help.

wxchic · Apr 24th 2013

Re: Find ActiveCell on another Sheet

You're right, it is working. I changed the code to include the entire range of merged cells...this fixed my issue. I also fixed the issue of one cell being pasted by using Resize into the F9:AB11 range. Thanks for all your help.

wxchic · Apr 22nd 2013

Re: Find ActiveCell on another Sheet

Good, that is what I want it to do. The user will be selecting a cell (variable) and searching for that value on another sheet. Should I activate the sheet they are on in the code even though they will have to be on that sheet since that is where the button will be? Or is it possible to add code to a worksheet instead of in module?

wxchic · Apr 22nd 2013

I'm trying to use the code below to find the ActiveCell on Worksheet("FORECAST") in non contiguous range on Worksheet("PUBLISH FORECAST") and do some cuttin and pastin. I think it is getting hung up on the fact that they are different sheets. It works if I search for activecell on same sheet. Any help would be fantastic. Thanks!

Code

Sub FindCity1()
     
    Dim msg
    
    Dim city1 As Range
    Dim citydata As Range
    Dim cityfound As Range
    Dim timefound As Range
    Dim citylist As Range
    
    
    Application.ScreenUpdating = False
    
     
     'What to look for
     
     
    With Worksheets("FORECAST")
    Set city1 = ActiveCell
    End With
    
             
     'Where to look
    With Worksheets("PUBLISH FORECAST")
    Set citylist = Worksheets("PUBLISH FORECAST").Range("B9,B10,B45,B80,B115,B150,B185,B220,B255,B290,B325,B360,B395,B430,B465,B500,B535,B570,B605,B640,B675")
    
     
     'Allow 'Not found' error
     On Error Resume Next
   
     
     'Do search
    Set cityfound = citylist.Find(what:=city1.Value, After:=citylist(1, 1), _
    LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByRows, _
    searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False)
     
     'Reset
   End With
     
     'Check if value found
    If Not cityfound Is Nothing Then
       'Copy and paste dates and times
         
    Set timefound = cityfound.Offset(0, 2)
    
    timefound.Copy
    Worksheets("FORECAST").Range("F6:F7").PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False
    
    'Copy and paste wnds
    
    cityfound.Offset(3, 2).Copy
        Worksheets("FORECAST").Range("F9:AB11").PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False
    
  
    Else
    
    msg = MsgBox("Station " & UCase(city1) & " was not found", vbInformation, "Sorry...")
    
    End If
     
     'Cleanup
    Set city1 = Nothing
    Set citylist = Nothing
    Set cityfound = Nothing
     
End Sub

Display More

wxchic · Apr 19th 2013

Re: Worksheet Change Malfunction

Yup I tried that and Im getting the "no cells found" error...as stated in post #6 above. I'm not sure exactly what this line is stating:

Code

Target.Address <> "$B$5" Then Exit

If someone changes B5 and hits enter, theoretically the address is now B6. So will it Exit the macro?

wxchic · Apr 19th 2013

Re: Skipping empty cell

Unable to upload file..getting error msg.

wxchic · Apr 19th 2013

I am using the code below to search a non-contiguous range for empty cells. If found, I am pasting into foundempty cell and pasting into offset of this foundempty. For some reason it will not paste in my first cell in range (B10). It works fine in the next foundempty in selected range. Any ideas?

Code

Sub findempty()
''
Dim foundempty As Range
Dim foundemptyData As Range
Dim forecastData As Range


Application.ScreenUpdating = False
'Check if station is selected from ready to publish box
   
If InRange(ActiveCell, Range("AG7:AG33")) Then
        ' code to handle that the active cell is within the right Range
MsgBox (Format(ActiveCell.Value) & " is already in publish list.  Please select current station.")
Exit Sub
           
Else
' code to handle that the active cell is not within the right Range
       
'Search city Range on Publish Forecast
  
Worksheets("PUBLISH FORECAST").Activate
Worksheets("PUBLISH FORECAST").Range("B10,B46,B81,B115,B145").Select
Selection.Find(what:="", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate


'call found cell foundempty
        
Set foundempty = ActiveCell
'Paste station value into foundempty
foundempty.Value = Worksheets("FORECAST").Range("B5").Value
'Paste station data into offset of foundempty
Worksheets("FORECAST").Range("E5:AB12").Copy
Set foundemptyData = foundempty.Offset(0, 1)
foundemptyData.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False


End If

Display More

wxchic · Apr 19th 2013

Re: Worksheet Change Malfunction

I am getting error when trying to upload example. Basically the cell(b5) being changed is a city. When a new city is put in, macros fetch the runways. These runways are then used on a line graph/chart. I have 16 rows sets up to receive runways. If an input city only has 4 runways I want to hide the empty rows so they are not shown on line graph/chart. I also need them to unhide each time before to allow for the possibility of all 16 rows being used.

I had it working, but it was also running when other macros would run on this page. I only want it ran when B5(city) is changed.

wxchic · Apr 19th 2013

Re: Worksheet Change Malfunction

The value of B5 is text. No need to evaluate. I only care if it is changed in any way. Here is the code I modified. I am getting an 'NO CELLS WERE FOUND' error when running now. Any ideas? Basically I need the range (B224:B239) to hide or unhide based on changing B5 changing.

Code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    
    
    Application.ScreenUpdating = False
        
    
    Dim runway As Range
    Dim shadow As Range
    
    
    If Target.Address <> "$B$5" Then Exit Sub
    
    Set runway = Range("C224:C239")
    Set shadow = Range("B224:B239")
    
    Range("$B$224:$B$239").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = Range("$B$5")
    
    shadow.Value = runway.Value
        
    Range("$B$224:$B$239").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
    
    
End Sub

Display More

wxchic · Apr 18th 2013

Re: Worksheet Change Malfunction

Sorry, Im muddling through this. Can you give me an example of a formal error handler? Thanks.

wxchic · Apr 18th 2013

Hello,

I'm using the crude code below to hide/unhide a range when cell B5 is changed. It works fine until I run another macro on the page. Then it unhides my rows, which I don't want. My question is, Is there a way to run this macro only when the cell B5 changes and nothing else?

Thanks.

Code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    
    Application.ScreenUpdating = False
        
    
    Dim runway As Range
    Dim shadow As Range
    
    Set runway = Range("C224:C239")
    Set shadow = Range("B224:B239")
    
    Range("$B$224:$B$239").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = False
    
    shadow.Value = runway.Value
        
    Range("$B$224:$B$239").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = _
    Target(1, 1).Address = "$B$5"
    
    
    
    On Error GoTo 0
End Sub

Display More

wxchic · Mar 20th 2013

Re: Find Blank in non contiguous range

what part is confusing?

Quote from jindon;654900

No idea about what you are trying to do.

wxchic · Mar 20th 2013

Re: Find Blank in non contiguous range

I'm testing this one, but somehow I need publishText = forecastText. Similar to copy paste. I'm not sure I need to name a range for publishText since it will be an offset location. But what is being pasted into this location is forecastText. Hope this makes sense.

wxchic · Mar 18th 2013

Re: Find Blank in non contiguous range

Yes. That is exactly what I'm saying.

Posts by wxchic

VBA Set new IE tab as variable

VBA Set new IE tab as variable

Drop down list

Drop down list

Drop down list

Consolidate rows VBA

Dynamic print area

Find ActiveCell on another Sheet

Find ActiveCell on another Sheet

Find ActiveCell on another Sheet

Worksheet Change Malfunction

Skipping empty cell

Skipping empty cell

Worksheet Change Malfunction

Worksheet Change Malfunction

Worksheet Change Malfunction

Worksheet Change Malfunction

Find Blank in non contiguous range

Find Blank in non contiguous range

Find Blank in non contiguous range